Select Page

Watch Excel Video 179 to get started with MATCH, a flexible Excel function that can add power to other functions. The syntax of MATCH is:
=MATCH(what to look for, where to find it, match type).
What to look for (lookup value) and the range to find it are very similar to VLOOKUP and HLOOKUP. The match type is a little different. Instead of true and false, match type can be 1 (largest value that is less than or equal to the lookup value with the data sorted in ascending order, similar to TRUE in VLOOKUP and HLOOKUP), 0 (exact match, similar to FALSE in VLOOKUP and HLOOKUP) and -1 (smallest value that is greater than or equal to the lookup value, with the data sorted in descending order).
Typically I use MATCH to find what I’m looking for either in one column or one row of data. Once Excel finds what I’m looking for, MATCH tells me where (which row or column) it found it. I’ll use that value in the INDEX function we talked about last time.
MATCH is not case-sensitive, but the really helpful thing about MATCH is that you can use wildcards. Wildcards are either ? (one character) or * (one or more characters). Watch Excel Video 179 to see how I can use wildcards when I don’t know exactly what I’m looking for. Be aware that MATCH will tell you the first time it finds something (VLOOKUP and HLOOKUP work the same way), so that if you have the same data multiple times in a list, MATCH will only find the first instance of the data.