Vlookup multiple criteria

A common query is how to Vlookup multiple criteria to extract an answer from a list. Although there are complex ways to do this we prefer the simplest way which in our mind means creating another column to help us with the answer.

NEW: Go to VLOOKUP Explanations with downloadable exercises and detailed solution

So if you have the following list in 2 separate columns but you want to perform a lookup so that it considers both the country and brand.

Column A Column B

ANGOLA Aerius
ANGOLA Celestamine
ANGOLA Clarinase
ANGOLA Claritine
ANGOLA Diprobase
ANGOLA Diprogenta
ANGOLA Diprosalic
ANGOLA Diprosone
ANGOLA Elocom
ANGOLA Elocon
ANGOLA Lotriderm
ANGOLA Nasonex
ANGOLA Quadriderm
ANGOLA Singulair

Our method would be to create a new column and assuming that the the first country is in cell A1, then in cell C1 we would join column A and column B using CONCATENATE i.e.

=CONCATENATE(A1,B1)

When you choose the lookup value you will need to do the same thing i.e. choose the Country and Brand and use this formula to join them together.

You can now use VLOOKUP in the normal way (see VLOOKUP video clip) but use the new column as the table.