Lookup
Finding a name in a list
Definition
Finding a name in a list means giving MATCH the name and the list so it returns that name's position.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The name to find, wrapped in double quotes. |
lookup_array |
The single column of names to search. |
match_type |
Use 0 for an exact match. |
Worked example
A rota stores names in B2:B4. Fay is the name to find.
| A: Seat | B: Name |
|---|---|
| 1 | Dan |
| 2 | Eve |
| 3 | Fay |
Formula: =MATCH("Fay",B2:B4,0)
Result: 3. Fay is the third name inside B2:B4.
Practice
Cells A2:A4 hold Ann, Bea, and Cam. In B2, write a formula that returns the position of Bea.
- In B2, write a MATCH formula that returns the position of Bea in A2:A4.
- B2 should show the position of Bea in A2:A4.
Step 1 of 2
Enter =MATCH("Bea",A2:A4,0) in B2.
Bea is the second item, so the position is 2.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses MATCH.
B2 now shows 2.
The next lesson is Finding a color in a list.
Related functions
MATCH is the function that returns the position of that name.
Common mistake
Leaving out the 0, as in =MATCH("Fay",B2:B4): the default match type expects sorted numbers and can return a wrong position or #N/A instead of 3.