Author: Oscar Cronquist Article last updated on January 25, 2019

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 products by this list, displayed in the above picture in cell range G3:G9.

This may seem to be a task that could take all week if you are working with a large list, however, Excel is great at these things. This task can easily be completed in under a minute.

Formula in cell F13:

=MATCH(B13, Sheet2!$G$3:$G$9, 0)

The MATCH function finds the relative position of an item in the list made by your boss. You then simply sort the new list from small to large, see detailed instructions below.

MATCH(lookup_value, lookup_array, [match_type]

lookup_value: B3 is a relative cell reference. This cell reference changes when the formula is copied. Learn more about absolute and relative cell references:

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

What happens in cell F13 when calculated?

MATCH(B3, Sheet2!$G$3:$B$G, 0)

becomes

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

and returns 4 in cell F13. Value "A" has position four in the lookup_array {"F"; "D"; "B"; "A"; "C"; "E"; "G"}.

The third argument in the MATCH function determines if it should get an exact match (not case sensitive).

Copy formula

  1. Select cell F13.
  2. Copy cell F13 (Ctrl + c).
  3. Select cell range F14:F19.
  4. Paste (Ctrl + v).

Sort data

  1. Right click on cell F13.
  2. Click Sort
  3. Click "Sort Smallest to Largest"

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!