Vlookup on two (or more) columns

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.

=VLOOKUP(A9,A2:C6,3,FALSE)

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:

=B2&C2

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.

The values from column B and C are merged (with time converted to numbers). You can hide column A, so the table looks cleaner. But if you do it, you have to remember to fill any additional cells.

You can also do it for multiple columns.