Home // Blog & Excel Tips // Vlookup multiple criteria

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.

 

Vlookup multiple criteria

Comments Closed
Uncategorised

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.