Subscribe!

The standard vlookup function allows for one condition to be looked up. For example, if I have the data in rows 1 through 7 below, and I want to determine the favorite color of Dan, who was born in Jan and is 16 years old, the standard vlookup function will not work.

vlookup

The standard vlookup function (in D11) returns Blue, which is the favorite color of the first Dan that it sees. There is a quick and easy way around this limitation in the standard vlookup function. You can concatenate, or combine the criteria you are searching for into one criteria. Lets revisit our example below…

Vlookup

I added column D which contains the formula =A2&B2&C2, this combines the name, the month and the age. We can now vlookup this combined data set using vlookup. See the new formula in E13 and the result in F13.

There are other ways of looking up multiple criterias in Excel such as arrays. This is by far the easiest way of getting the job done.

*UPDATED*

Excel Yogi has created the VlookupPlus Excel add in that allows for multiple criteria lookups. Read more and get it here:

http://articles.excelyogi.com/multiple-condition-vlookup-function-add-in-excel/2009/03/16/

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Related posts:

  1. Vlookup – My Favorite Function Vlookup...
  2. Multiple Condition Vlookup Function Add-in – Excel The question we are asked most often by ExcelYogi readers...
  3. Vlookup the Last Occurence of an Item – Excel The title of this post is slightly misleading. We will...
  4. Conditional Formatting with Multiple Criteria in Excel Microsoft Excel has a limit of 3 criteria in Conditional...
  5. Countif with Two Criteria Q: I need to count the number of times an...

This entry was posted on Monday, February 9th, 2009 at 2:46 pm and is filed under Excel Hints, Excel Quick Tricks. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Vlookup Multiple Criteria in Excel”

  1. vic j on February 11th, 2009 at 5:49 pm

    TOO EASY! if only I discovered your post two days ago I would have saved myself a lot of manual work arounds.
    Nice Post!

  2. Excel Yogi’s » Blog Archive » Multiple Condition Vlookup Function Add-in - Excel on March 16th, 2009 at 2:51 pm

    [...] question we are asked most often by ExcelYogi readers is how can I use vlookup with multiple criteria or conditions. There are several solutions but some can be pain staking and unrealistic. Knowing that the Excel [...]

  3. Nirav on April 27th, 2009 at 9:08 am

    Nice Tip, Many Thanks………

  4. Shan on February 22nd, 2010 at 7:08 am

    Joseph Harris get concept i been working this for atleast two weeks know. It is really useful to me. Many thanks.

Leave a Reply

You must be logged in to post a comment.