Lookup
Finding a color in a list
Definition
Finding a color in a list means giving MATCH the color and the list so it returns which slot that color occupies.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The color to find, wrapped in double quotes. |
lookup_array |
The single column of colors to search. |
match_type |
Use 0 for an exact match. |
Worked example
A paint list stores colors in B2:B5. fir is the color to find.
| A: Tin | B: Color |
|---|---|
| 1 | ash |
| 2 | oak |
| 3 | elm |
| 4 | fir |
Formula: =MATCH("fir",B2:B5,0)
Result: 4. fir is the fourth color inside B2:B5.
Practice
Cells A2:A5 hold red, blue, green, and gold. In B2, write a formula that returns the position of green.
- In B2, write a MATCH formula that returns the position of green in A2:A5.
- B2 should show the position of green in A2:A5.
Step 1 of 2
Enter =MATCH("green",A2:A5,0) in B2.
green is the third item, so the position is 3.
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 3.
The next lesson is Choosing a word from a text test.
Related functions
MATCH is the function that returns which slot that color occupies.
Common mistake
Leaving out the 0, as in =MATCH("fir",B2:B5): the default match type expects sorted numbers and can return a wrong position or #N/A instead of 4.