Normally, when you use VLOOKUP, you are going to check a single condition. VLOOKUP can’t use more than one condition until you modify it a little bit.
VLOOKUP on a single column
In this example, we are going to display a ticket price for a movie. Let’s say we choose The Lion King.
As you can see the price of the ticket is $6. Let’s create a VLOOKUP table to calculate this price.
Now, let’s try a different example. This time we are going to check the price of the movie that is screened at the particular time. For these reasons, we have to add a second criterion – time.
In the next example, the same movies are displayed more than once at different times.
Now, if you ran a similar formula, you are going to get the first value (from $6, and $8.5) which is $6. Excel looks for the first matching criterion and returns it as a result.
VLOOKUP on two or more columns
The question is – how to use VLOOKUP based on two criteria, instead of one? Probably you’ve checked other tutorials that use concatenate operator and approximate match. It’s a quite simple method, unfortunately, it doesn’t work.
I’m going to show you a way you can achieve this.
In this method, you are going to use a helper column. In this column, all values from the current row (instead of the last cell) are going to be concatenated.
In column A2 insert this formula:
Fill the rest of the cells for the current table column (A1:A6) and also for cell A9. This is how it should look like.
Column A is now our lookup column.
You can also do it for multiple columns.