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

*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(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)

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.

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

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

Compare two tables: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

Highlight records – multiple criteria [OR logic]

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

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 occurring in only one list

Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on […]

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

Sort dates within a date range

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]

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 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 […]

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form