MATCH

Posted on 18-01-2020 , by: admin , in , 0 Comments

previous
NEXT

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:

Using the 0 option suitable for an exact match. The Ascending list gives the exact match. The Descending list gives the exact match. The Wrong Value list cannot find an exact match, so the #NA is shown.
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:

Using the 1 option suitable for a ascending list to find an exact or next lowest match. The Ascending list gives the exact match. The Descending list gives the #NA error. The Wrong Value list finds the next lowest number..
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:

Using the -1 option suitable for a descending list to find an exact or next highest match. The Ascending list gives the #NA error. The Descending list gives the exact match. The Wrong Value list finds the next highest number.
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:

The tables below were used to by a bus company taking booking for bus tours. They need to allocate a bus with enough seats for the all the passengers. The list of bus sizes has been entered in a list. The number of passengers on the tour is then entered. The =MATCH() function looks down the list to find the bus with enough seats. If the number of passengers is not an exact match, the next biggest bus will be picked. After the =MATCH() function has found the bus, the =INDEX() function has been used to look down the list again and pick out the actual bus size required.
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:

The tables below were used by a school to calculate the exam grades for pupils. The list of grade breakpoints was entered in a list. The pupils scores were entered in another list. The pupils scores are compared against the breakpoints. If an exact match is not found, the next lowest breakpoint is used. The =INDEX() function then looks down the Grade list to find the grade.
Exam Score Grade
0 Fail
50 Pass
90 Merit
95 Distinction

 

 

 Pupil ScoreGrade
Alan60Pass
Bob6Fail
Carol97Distinction
David89Pass
   =INDEX (D111:D114, MATCH (G114,C111:C114,1),0)

 

 

previous
NEXT
share:

Leave a comment