The 10 Excel Formulas That Will Make You Look Like a Genius – Number 2 MATCH
In the last article we talked about VLOOKUP. If you’d like to see it, you can find it here: The 10 Excel Formulas That Will Make You Look Like a Genius – Number 1 VLOOKUP | LinkedIn
Today, we’re going to talk about the second formula that will make you look like a genius: MATCH
Number Two –MATCH
=MATCH(lookup_value, lookup_array, match_type)
The second formula that will make you look like a genius is MATCH. The MATCH formula helps us find the position of something on the tab. It is particularly useful as a nested function.
A what? Nested function?!
Nesting functions is just a way of using formulas together. Just like you can do both addition and multiplication in the same formula, you can also mix other formulas together.
Why can’t I just pick the right formula for what I need to do?
I learned the need to write nested formulas early in my professional career. I was working in a financial planning and analysis department and had only recently discovered the VLOOKUP formula. That changed my world. For the first time I was able to build real financial models that dynamically updated when someone made an adjustment in the master file.
I was meticulous. I studied the master file and knew that January data was in Column D, February was three columns to the right of that, and the same for all of the other months. I set up my January column to reference column 4, February to reference column 7 and so forth until the entire file had been mapped.
This worked really well until one day, while my analysis tool was closed, one of the other analysts went into the master file and did some ad-hoc analysis by adding a column between June and July. That shifted all months from July through December to the right by one and effectively broke my meticulously crafted VLOOKUPs. Unfortunately, I did not notice this change before sending my boss the latest updates from my tool that were now wrong for the second half of the year. I was so embarrassed.
That was when I started looking for a way to strengthen the one good formula I knew. Was there a way to have VLOOKUP find the correct column to reference without specifically telling it to look in a particular column? That is where MATCH came in.
By nesting MATCH into the column reference of my VLOOKUP formula, I was able to instruct VLOOKUP to dynamically find the correct column by looking for a specific column header.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=MATCH(lookup_value, lookup_array, match_type)
=VLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_array, match_type), [range_lookup])
Now let’s talk about what that looks like in practice. Remember the example we used in the VLOOKUP section:
Add alt text
When we wanted to find Jamal’s age we used this VLOOKUP formula: =VLOOKUP(“Jamal”,B3:E7,3,false)
We could nest the MATCH formula like this: =VLOOKUP(“Jamal”,B3:E7,MATCH(“Age”,B3:E3,0),FALSE)
By adding the MATCH formula to find the column header “Age” we are able to dynamically change the column reference to wherever “Age” is in the list of headers.
Let me show you how the VLOOKUP formula reacts when the position of “Age” changes from column D to column E:
Add alt text
As long as the positions of the columns do not change, the VLOOKUP works properly. However, if the column position is changed (“Age” was shifted one column to the right), the VLOOKUP fails:
Add alt text
Notice that when I inserted a column to the left of “Age”, the VLOOKUP formula dynamically updated the range. The original formula was
=VLOOKUP(“Jamal”,B3:E7,3,false)
And when I added a column, Excel automatically updated the range to include the new column:
=VLOOKUP(“Jamal”,B3:F7,3,false)
Excel is programmed well enough that when rows or columns are inserted inside a set of data, the formulas that use that range of data are automatically updated. However, the hardcoded number “3” in the formula will not get updated.
Note: Excel will automatically update the range used in a formula if the formula references a spreadsheet that is currently open on the user’s computer. It will not automatically update if the referenced spreadsheet is not open on the user’s computer.
The MATCH formula overcomes this weakness by allowing the column reference (“3”) to be dynamically defined by the formula. Now, without changing the formula in either scenario, the new and improved VLOOKUP does not fail.
=VLOOKUP(“Jamal”,B3:E7,MATCH(“Age”,B3:E3,0),FALSE)
Add alt text
Add alt text
Some closing remarks about the MATCH formula:
The MATCH formula can end with a 1, 0, or -1. Of these three, the only one I have found useful in my roles is “0”. The “0” returns either an exact match or else a #N/A error value. The “1” and “-1” match types only work with numerical lists and only if those numerical lists are sorted in order (ascending for “1” and descending for “-1”). Match type “1” will return the largest value that is less than the lookup value. Match type “-1” will find the smallest value that is greater than the lookup value.
Now, here is why you don’t need to know any of that and can just remember to use “0”. The MATCH formula returns an actual number (in our example a column reference), and actual numbers can be added or subtracted. In order to return Jamal’s birth month, we can use the same formula as before and just add 1 behind the MATCH formula.
=VLOOKUP(“Jamal”,B3:E7,MATCH(“Age”,B3:E3,0)+1,FALSE)
Add alt text
Excel is great about allowing users to build one formula on top of another to create some really amazing functions.
Have fun with your new dynamic VLOOKUP-MATCH combination.
Download the VLOOKUP-MATCH practice sheet below:
