Author: Oscar Cronquist Article last updated on August 21, 2017

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 to 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. Learn more about absolute and relative cell references:

How to use absolute and relative references

What is a reference in Excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

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)


=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.

How to use the MATCH function

Identify the position of a value in an 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)

Function in this blog post

How to use the MATCH function

Identify the position of a value in an array.

Recommended blog posts

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

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2  (array formula) How to […]

Sorting numbers and text cells also removing blanks using an array formula

Question: How do I sort text and numbers and also removing blanks using an array formula? Answer: Array formula in […]

insertexcerpt id="1953"]