Match
What Does It Do?
This function looks for an item in a list and shows its position. It can be used with text and numbers. It can look for an exact match or an approximate match.
Syntax
=MATCH(WhatToLookFor, WhereToLook, TypeOfMatch)
The TypeOfMatch either 0, 1 or -1.
Using 0 will look for an exact match. If no match is found the #NA error will be shown.
Using 1 will look for an exact match, or the next lowest number if no exact match exists. If there is no match or next lowest number the error #NA is shown. The list of values being examined must be sorted for this to work correctly.
Using -1 will look for an exact match, or the next highest number if no exact match exists. If there is no exact match or next highest number the error #NA is shown. The list must be sorted for this to work properly.
Names | |
Bob | |
Alan | |
David | |
Carol |
Type a name to look for : | Alan |
The position of Alan is : | 2 |
Values | |
250 | |
600 | |
1000 | |
4000 |
Type a value : | 1000 |
Value position : | 3 |
=MATCH(I9,I4:I7,1) |
Example 1:
Ascending |
10 |
20 |
30 |
40 |
20 |
2 |
Descending |
40 |
30 |
20 |
10 |
20 |
3 |
=MATCH(G45,G40:G43,0) |
Wrong Value |
10 |
20 |
30 |
40 |
25 |
#N/A |
Example 2:
Ascending |
10 |
20 |
30 |
40 |
20 |
2 |
Descending |
40 |
30 |
20 |
10 |
20 |
#N/A |
Wrong Value |
10 |
20 |
30 |
40 |
25 |
2 |
=MATCH(G62,G57:G60,1) |
Example 3:
Ascending |
10 |
20 |
30 |
40 |
20 |
#N/A |
Descending |
40 |
30 |
20 |
10 |
20 |
3 |
Wrong Value |
40 |
30 |
20 |
10 |
25 |
2 |
=MATCH(G79,G74:G77,-1) |
Example 4:
Bus Size | |
Bus 1 | 54 |
Bus 2 | 50 |
Bus 3 | 22 |
Bus 4 | 15 |
Bus 5 | 6 |
Passengers on the tour : | 23 |
Bus size needed : | 50 |
=INDEX(D95:D99, MATCH(H94,D95:D99,-1),0) |
Example 5:
Exam Score | Grade |
0 | Fail |
50 | Pass |
90 | Merit |
95 | Distinction |
Pupil Score | Grade | |
Alan | 60 | Pass |
Bob | 6 | Fail |
Carol | 97 | Distinction |
David | 89 | Pass |
=INDEX (D111:D114, MATCH (G114,C111:C114,1),0) |