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.

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…

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...