VLOOKUP

We all know and love VLOOKUP - it’s been with us since the very beginning of Excel and it gave business people array indexing, which was previously only really used by programmers.

More than 30 years later VLOOKUPs are still used extensively in the workplace. This is mainly due to user’s familiarity with the tool and its ease of use for single value lookups. If you want to lookup two or more values however, VLOOKUP requires a bit more faffing around to get the task done.

First off you would need a helper column where you concatenate the two varible combinations you want to look up.

This helper column can then be used as the lookup value in the formula.

Lets mention having to use numbered column indexes here. In a dinky little table like the one shown above counting columns isn’t really a big deal. This little table does not reflect reality in business however where many Excel documents have literally dozens of columns. A solution to this is to add another row at the top of the table and number the columns so you can quickly reference the column you want.

So what happens when a user asks: “Hey can we add in a column that shows…..”?
Answer: Depending on the number of columns and VLOOKUPs in your document, anything from slight inconvenience to absolute chaos! This will inevitably lead to a call from another user: “Hey, file X is broken..”

And lets not forget that the returned values in a VLOOKUP need to be to the right of the lookup value in the spreadsheet or the VLOOKUP won’t work at all.

There has to be a better way right?
Right!

One would assume that the powerful INDEX MATCH combo would be ubiquitously used in place of VLOOKUPs by now, given that it has been around for a while - but even some data vloggers still demonstrate complex VLOOKUPs without even mentioning alternatives!
If you’ve never heard of this before, I would suggest looking into this pair of functions BUT there is something just as good and much easier to use that came out in 2019…

XLOOKUP

Does this care if your return values are to the right of your lookup value or not?
No!

Does it care if you add or remove columns from the lookup area?
No!

Does it need you to create a helper column to look up 2 or more values?
No!

Should we ditch VLOOKUP and use XLOOKUP from now on?
Well yes!

It is much easier to use than V. Lets look at the syntax for the above scenario where we are returning a result based on two lookup values.

XLOOKUP(LookUpValue1 & LookupValue2, LookupColumn1 & LookupColum2, ReturnValueColum)

And that’s it. No index required, so no need to count columns and no need to worry about column positions if you add or remove some.