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!

Download excel tutorial file

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

Want to learn more about formulas and sorting? You must read these posts:

Related posts:

Sort a list in random order in excel

Sort values in a table (vba)

Sort values in a cell using a custom delimiter (vba)

Sort text values by length using array formula in excel

Create and sort distinct list by adjacent cell value size