How to Link Cells for Sorting in Excel (5 Methods)

Below is the sample Daily Sale dataset to which we want to link cells in the Link Cells for Sorting dataset.

Method 1: Link Cells for Sorting Using Absolute Reference

Step 1: Insert Absolute References in cells of cells that you want to link to.

=$I$11

We want to link the C5 cell to the I5 cell. That’s why we put an absolute reference of cell I5.

Input absolute references in all cells.

Step 2: Select the entire Link Cells for Sorting dataset, Go to the Home tab > Editing section > Select Sort & Filter > Choose Custom Sort.

Step 3: The Sort command window pops up. In the window,

Choose Total Price as Sort by, Cell Values as Sort On, and Largest to Smallest as Order option.

Click on OK.

➤ You see the highest Total Price cell values tops in the dataset along with the dates as shown in the image below.

Inserting an Absolute Reference to solve sorting issues works and also links respective cells.

Method 2: Using INDEX – MATCH Functions to Link Cells for Sorting

Step 1: Paste the formula below in any cell where you want to link the cell that satisfies the criteria.

=INDEX(I$5:I$16,MATCH(B5,$E$5:$E$16,0))

In the formula, I$5:I$16 refers to the array argument. The MATCH portion MATCH(B5,$E$5:$E$16,0) declares the row_num. And the MATCH portion assigns B5 as lookup_value, $E$5:$E$16 as lookup_array, and 0 declares the [match_type] as an exact match.

The used MATCH portion returns 1 as it finds 03/02/22 in row number 1 within the array.

Step 2: Press ENTER and drag the Fill Handle to apply the formula to other cells.

Step 3: Repeat Steps 2 and 3 of Method 1 to execute Sorting. The Largest to Smallest sorting results in placing bigger values on tops. You can apply any of the Sorting options and static cell references won’t be an issue.

Method 3: Link Cells in Excel Using VLOOKUP Function

Step 1: Insert the following formula in any blank cell (i.e., C5).

=VLOOKUP(B5,$E$5:$I$16,5,FALSE)

In the formula,

B5 = lookup_value

$E$5:$I$16 = table_array

5 = column_index_num

FALSE = [range_lookup]

Step 2: Use the ENTER key to execute the formula and drag the Fill Handle to apply the formula to other cells.

Step 3: Follow Steps 2 and 3 of Method 1 to execute sorting.

Method 4: Using XLOOKUP Function to Link Cells

Step 1: Insert the following formula in any cell (i.e., C5).

=XLOOKUP(B5,$E$5:$E$16,$I$5:$I$16,"",0)

Comparing values to the arguments,

lookup = B5

lookup_array = $E$5:$E$16

return_array = $I$5:$I$16

[not_found] = “”

[match_mode] = 0

Step 2: Use the Fill Handle to apply the formula to other cells.

Step 3: Execute Steps 2 and 3 of Method 1 to apply sorting.

Method 5: Using FILTER Function to Fetch Required Columns for Sorting

Step 1: Use the following formula to fetch particular columns.

=FILTER(H6:K16, G6:G16=C4)

The values in the formula refer

H6:K16 = array

G6:G16=C4 = include, works as criteria.

Step 2: Hit ENTER, Excel fetches all the required columns.

Step 3: Highlight the entire range, Right-Click on it. The Context Menu appears. In the Context Menu, Select Copy.

Step 4: Highlight the same range as you did in the previous step. Right-click on it. The Context Menu appears. From the Context Menu, Choose Paste Options Value.

Step 5: After pasting formulas as values, use Steps 2 and 3 of Method 1 to apply Custom Sort by Total Price.

You can choose other options offered in the Custom Sort window to execute Custom Sort. FILTER resultant values can’t be sorted so you must paste them as values in the range.

Download Excel Workbook

Link Cells for Sorting.xlsx