Custom sorting rules in Excel - Cell-by-Cell Formula Trace

Sample Data

List of fruits with assigned priority numbers to define a custom sort order.

Cell	Value
A1	Fruit
A2	Banana
A3	Apple
A4	Cherry
A5	Date
B1	Priority
B2	3
B3	1
B4	2
B5	4
C1	SortOrder
C2
C3
C4
C5

Formula Trace

=MATCH(A2, {"Apple","Cherry","Banana","Date"}, 0)

Step 1: MATCH("Banana", {"Apple","Cherry","Banana","Date"}, 0)

Cell Reference Map

    A       B       C
1 | Fruit | Priority | SortOrder
2 | Banana|    3    |  =MATCH(A2, {"Apple","Cherry","Banana","Date"}, 0)
3 | Apple |    1    |  =MATCH(A3, {"Apple","Cherry","Banana","Date"}, 0)
4 | Cherry|    2    |  =MATCH(A4, {"Apple","Cherry","Banana","Date"}, 0)
5 | Date  |    4    |  =MATCH(A5, {"Apple","Cherry","Banana","Date"}, 0)

Column A contains fruits, column B their priority numbers, and column C uses MATCH formula to assign sort order based on a custom list.

Result

    A       B       C
1 | Fruit | Priority | SortOrder
2 | Banana|    3    |    3
3 | Apple |    1    |    1
4 | Cherry|    2    |    2
5 | Date  |    4    |    4

The SortOrder column shows the position of each fruit in the custom list, which can be used to sort the data accordingly.

Sheet Trace Quiz - 3 Questions

What does the MATCH formula return for 'Apple'?

A 3

B 1

C 2

D 4

Key Result

MATCH(value, {custom_list}, 0) returns the position of value in the custom list for sorting.