Your boss wants you to sort the company´s products by a new criterion, quality.

You receive a list from your boss and now you have to sort your products by this list. You copy the list into sheet2.

Formula in cell F3:

=MATCH(B3, Sheet2!\$B\$3:\$B\$9, 0)

MATCH(lookup_value, lookup_array, [match_type]

lookup_value: B3 is a relative cell reference. This cell reference changes when the formula is copied.

lookup_array: Sheet2!\$B\$3:\$B\$9 is an absolute cell reference. It doesn´t change when copied. You toggle cell references by selecting cell reference in formula bar and then press F4.

What happens in cell F3 when calculated?

MATCH(lookup_value, lookup_array, [match_type]

=MATCH(B3, Sheet2!\$B\$3:\$B\$9, 0)

becomes

=MATCH("A", {"F"; "D"; "B"; "A"; "C"; "E"; "G"}, 0)

and returns 4 in cell F3. Value "A" has position four in the lookup_array.

Copy formula

1. Select cell F3.
2. Copy cell F3 (Ctrl + c).
3. Select cell range F4:F9.
4. Paste (Ctrl + v).

Sorting table

1. Select cell range B3:F9.
2. Right click on selection.
3. Click Sort and then click Custom Sort...
4. Sort by "Sort", (column F) and order: Smallest to Largest
5. Click OK!

custom sort.xls
(Excel 97-2003 Workbook *.xls)

Functions in this blog post:

MATCH(lookup_value, lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value

Match_type
-1 : Match finds the largest value less than or equal to lookup_value. Lookup_array must be placed in ascending order.
0  : Match finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
1  : Match finds the smallest value greater than or equal to lookup_value. Lookup_array must be placed in descending order.

Recommended blog posts