## How to sort a table in a custom order in excel [No formula]

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(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 in excel

What is a refererence 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)

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.

How to use Excel’s MATCH function

Identify the position of a value in an array.

### Copy formula

- Select cell F3.
- Copy cell F3 (Ctrl + c).
- Select cell range F4:F9.
- Paste (Ctrl + v).

### Sorting table

- Select cell range B3:F9.
- Right click on selection.
- Click Sort and then click Custom Sort...

- Sort by "Sort", (column F) and order: Smallest to Largest

- Click OK!

### Download excel tutorial file

custom sort.xls

(Excel 97-2003 Workbook *.xls)

### Function in this blog post

How to use Excel’s 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:

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"]

Quickly highlight records in a list using multiple criteria in excel

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]Compare two lists of data: Highlight common records in excel

Overview In this blog post you will learn how to: Create a countifs function Create a conditional formatting formula and […]Compare two lists of data: Highlight records existing in only one list in excel

Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on […]Compare two lists of data: Filter records existing in only one list in excel

In this example we are going to use two lists with identical columns in excel 2007. It is easy to […]Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]