Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get Expert help in seconds.

Your message must be at least 40 characters
Our professional Expert are available now. Your privacy is guaranteed.

# VLOOKUP with Multiple Criteria

VLOOKUP is one of the most used functions for lookup in Excel. One of the shortcomings of this function is it only works for looking up data having a single criterion. Yet, with a simple workaround, we can make VLOOKUP work with multiple criteria. In this tutorial, we will learn to use VLOOKUP with multiple criteria.

Figure 1. Data set for VLOOKUP with Multiple Criteria

## Syntax for Using VLOOKUP with multiple criteria

VLOOKUP formula:

`=VLOOKUP(lookup_value, table_array, col_index_num, [rng_lookup])`

The VLOOKUP formula fails to look up data having multiple criteria. VLOOKUP with multiple criteria is looking for data including several criteria. We need to make some modifications for it

to work with multiple criteria. The formula will now be,

`=VLOOKUP(lookup_val1&lookup_val2, table_array, col_index, [range_lookup])`

## Setting Up Data

Let’s assume we have a movie theatre data set. It has several unique columns such as the movie names, showtime and ticket prices.

Looking at the data in B2 to C7, we can see that the movie in B11- “Jurassic World 3D” has two records. The price for the first show at 15:00 costs \$6.00. The next show at 20:00 costs \$8.00.

If we tried to find the price for the premiere at 20:00 with the formula `=VLOOKUP(B11, \$B\$3:\$D\$7, 3, FALSE)`, it will show the wrong result. To find the results for multiple criteria using VLOOKUP, we need to make some changes to the formula.

Figure 2. Incorrect Result with VLOOKUP with Single Criterion

The Concatenate Operator(“&”) offers great help to use VLOOKUP with multiple criteria. Before using the concatenate operator, VLOOKUP could not return the correct price for the second show. It did not meet the two criteria( i.e. the Movie name and Showtime).

With help of the concatenate operator, we have specified the two criteria inside the formula. Now, it extracts the correct price for the second show. However, we still have to make sure the range_lookup for VLOOKUP  is set to the default value which is TRUE.

To find the correct price for “Jurassic World 3D” at 20:00, we have to :

• Select cell D11 by clicking on it.
• Insert the formula `=VLOOKUP(B11&C11,\$B\$3:\$D\$7,3)`”.
• Press Enter.

Figure 3. Example of How to use VLOOKUP with Multiple Criteria

This will return the result \$8.00 this time. We can be certain that this is the accurate price for the movie “Jurassic World 3D” at “20:00” by matching it with cell D7.

This formula allows us to combine several criteria and use them as one in the VLOOKUP function. It is an easy solution for getting the desired result with several criteria.

## Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.

### Did this post not answer your question? Get a solution from connecting with the expert.

Solution examples
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
I'm looking for a formula or format for if there is data in c4 on a sheet... It adds extra information underneath
Solved by X. E. in 20 mins
hi, my vlookup wont work in some rows but does in others. its temperemental. e.g it will work on rows 10 to 15, then not row 16 and 17, but the will for the rest.
Solved by K. Q. in 21 mins
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins