VLOOKUP with multiple criteria
The VLOOKUP function cell D16 looks for both a value in column B and another value in column C. If both values match a third value on the same row from column D is retrieved and shown in cell D16.
Array formula in D16:
To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Explaining formula in cell D16
Step 1  Filter records
The IF function filters records that match the value in cell D15, all remaining records are blank. The picture below shows the array in cell range B3:F12.
Step 2  VLOOKUP value and return value from column to cell D16
VLOOKUP(D14,IF(C3:C12=D15,B3:F12,""),3,FALSE)
becomes
VLOOKUP(D14,{"","","","",""; "","","","",""; "","","","",""; "","","","",""; "A","South America","The New Firm","Tory","Byrnes"; "","","","",""; "","","","",""; "","","","",""; "","","","",""; "F","South America","Trans United Airways","Ervin","Hennessey"},3,FALSE)
and returns "The New Firm" in cell D16.
VLOOKUP and a table
If you convert your cell range to a table you can add or remove as many records to the table as you want and the cell reference in the formula is automatically adjusted. Use the table name and table column name in the vlookup function to achieve this. See the formula bar in the picture below.
How to convert a cell range to a table (Excel 2007 and above)
 Select cell range A7:E17
 Go to tab "Insert"
 Click table button
 Enable "My table has headers"
 Click OK
Learn more about excel tables:
Become more productive โ Learn Excel Defined Tables
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
Become more productive โ Learn Excel Defined Tables
VLOOKUP  Select a table column with a drop down list
The following sheet letยดs you select a column in the table and the the value from that column is returned.
How to create the drop down list in cell B4
 Select cell B4
 Go to "Data" tab
 Click "Data Validation" button
 Select "List" i the drop down list

Select source: =$C$8:$E$8
 Click OK
You can't use cell references pointing to an excel defined table (structured references) in a drop down list or in conditional formatting formulas unless you use this workaround:
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to [โฆ]
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
Array formula in cell C4:
becomes
=VLOOKUP(C2, IF(Table2[Region]=C3, Table2, ""), MATCH(Company Name", {"Item","Region","Company Name","First Name","Last Name"}, 0), FALSE)
becomes
=VLOOKUP(C2, IF(Table2[Region]=C3, Table2, ""), 3, FALSE)
VLOOKUP and two conditions (date range)
Array formula in cell C5:
This article explains how to lookup and return multiple values:
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
5 easy ways to VLOOKUP and return multiple values
The following formula returns an array with the same size as cell reference Table3[Date]
(Table3[Date]>=C3)*(Table3[Date]<=C4) returns {0;1;1;1;1;1;1;0;0;0} . So the first value in the array corresponds to the first record in the table. Example, 0 (zero) is FALSE and that record is not between 20120102 and 20120107.
The formula becomes
=VLOOKUP(C2,IF({0;1;1;1;1;1;1;0;0;0},Table3,""),3,FALSE)
becomes
=VLOOKUP(C2,IF({0;1;1;1;1;1;1;0;0;0},{"A", 40909, "Atlantic Corporation", "Birdie", "Schneider";"B", 40910, "Uplink Corporation", "Amshula", "Canty";"C", 40911, "Omni Consumer Products", "Jaycee", "Martin";"D", 40912, "Galaxy Corp", "Tracy", "Tuck";"A", 40913, "The New Firm", "Tory", "Byrnes";"B", 40914, "ZiffCorp", "Santos", "Cowart";"C", 40915, "Minuteman Cafe", "Gen", "Lindgren";"D", 40916, "Demo Company", "Edwin", "Shinn";"E", 40917, "Western Gas & Electric", "Allayna", "Egan";"F", 40918, "Trans United Airways", "Ervin", "Hennessey"},""),3,FALSE)
becomes
=VLOOKUP("A",{"","","","","";"B",40910,"Uplink Corporation","Amshula","Canty";"C",40911,"Omni Consumer Products","Jaycee","Martin";"D",40912,"Galaxy Corp","Tracy","Tuck";"A",40913,"The New Firm","Tory","Byrnes";"B",40914,"ZiffCorp","Santos","Cowart";"C",40915,"Minuteman Cafe","Gen","Lindgren";"","","","","";"","","","","";"","","","",""},3,FALSE)
INDEX and MATCH
The vlookup function can only look for values in first column of the table_array. The formula below demonstrates how to do a lookup in any table column and return a value from any table column.
Formula in cell C3:
Learn more about the INDEX and MATCH functions:
Gets a value in a specific cell range based on a row and column number.
Identify the position of a value in an array.
becomes
=INDEX(Table4[Item],4)
becomes
=INDEX({"A"; "B"; "C"; "D"; "A"; "B"; "C"; "D"; "E"; "F"},4)
and returns D in cell C3.
Download excel *.xlsx file
Vlookup  multiple conditions.xlsx
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found [โฆ]
VLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Answer: Array formula in [โฆ]
Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to [โฆ]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row [โฆ]
Choose between two data sets to VLOOKUP
Question: How do I search a specific data set, I have two tables to choose from? Answer: Formula in cell C13: [โฆ]
Return multiple values if in range
The image above shows a formula in cell C11 that extracts values from column D if the number in cell [โฆ]
27 Responses to โVLOOKUP with multiple criteriaโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Wow I have never thought of using the Table like that...... really potent stuff!
chrisham,
Thank you for commenting! You can use arrays in most excel functions.
I am having a problem getting vlookup to work when asking it to check two different tables based on what data is percent in specific cells. Basically I want it to check one table if a persons gender is male, and another table if the gender is female. Any idea how I can accomplish this? I would really appreciate any help I could get. Thank you very much.
Hi Oscar,
I want to sum up results of Vlookup
please help
For eg,
Product No in Column A and qty sold in Column B
I Will need to derive qty sold of each product. It's a huge data of 2000 odd product list
please suggest
Vicky,
Here is a post about pivot tables:
https://www.getdigitalhelp.com/2012/11/09/analyzetrendsusingpivottables/
You can also use formulas. How to extract a unique distinct list:
https://www.getdigitalhelp.com/2009/03/30/howtoextractauniquelistandtheduplicatesinexcelfromonecolumn/
How to use the sumif function:
https://www.getdigitalhelp.com/2009/11/16/sumvaluesbetweentwodateswithcriteriainexcel/
Remove the date criteria.
Hi OScar
SUMIFS,has worked great for me, Thanks for your help. Its been completely Superb
Hi Oscar,
Just a general question, which one is better for huge lookups
Vlookup or Index + Match
can we use index + match instead of vlookup, even if we need to extract values from the right side columns
Thanks
Krishna
Krishna,
Yes, read this: https://exceluser.com/blog/1107/whyindexmatchisfarbetterthanvlookuporhlookupinexcel.html
Hi Oscar.. I'm struggling using a lookup formula for extracting values from a large table.. it's something like:
Fruit Market
Apple Walmart
Pear Sigo
Grapes Sigo
Cherry Walmart
Orange 7eleven
The idea is just extract the fruits from Walmart in a new table but excluding the rest of the fruits.. What do you suggest??? Thanking you in advance for your help...
Jose,
Array formula in cell D2:
=INDEX($A$2:$B$6, SMALL(IF($B$2:$B$6=$D$1, MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), ""), ROW(A1)),COLUMN(A1))
Read more:
How to return multiple values using vlookup
Dear Oscar,
I want to vlookup with 2 conditions.
I want to search for value by using two criteria. ex: vlookup with invoice number and serial number search for the discription in other sheet or other related information. Each invoice number has multiple serial numbers or items.Ex. invoice number 1 has 1to 5 items, I need search for all 5 items from database to the main sheet.
Please help
Simao Pereira,
Can you provide some data and the desired outcome?
Awesome formula:
=VLOOKUP(C2,IF(B8:B17=C3,A8:E17,""),3,FALSE)
Thanks for sharing this technique.
=VLOOKUP(C2,IF(B8:B17=C3,A8:E17,""),3,FALSE)
Did anyone check this formula ? I get "Value" error (a value used in this formula is of the wrong data type). Any ideas ? ...
Andrei,
It is an array formula.
Select a cell
Type the array formula
Press and hold Ctrl + Shift simultaneously
Press Enter
Release all keys
If you did it right you now have curly brackets before and after the formula, in the formula bar.
It works. I've never done this before. I just feel so silly sometimes  whenever I think I know Excel, something new appears that makes me look like a hairstylist. :(
Hi,
Perhaps you can help me with a similar formula.
I am trying to keep track of components for manufacturing purposes. I have one table where i keep my manufacturing data (product, quantity and date) each of my products require a unique valve and i want to have another table where i can look up the item and if it falls in a particular month indicate the total quantity.
My goal here is to be able to accurately forecast my valve needs.
I have attached an example. https://postimg.org/image/3uvet6l2f/
The incoming valve column i use for valve orders that i place. the cylinder production column is where i need the formula to match the item and time period and return the results.
Thank you very much!
Hi Oscar,
Please how do I handle this case...
An excel column has up to a thousand entries and each entry is to be looked up in another column. The aim is to find out if their is any entry among the up to one thousand entries which is in the other column or not. Since the number of entries to be looked up for is large, I want to avoid doing this one at a time.
Ray,
Use the match function to see if an entry exists in another column.
MATCH(A1,C1:C100000,0) If it returns a number it exists, an error it does not exist.
If we have a data like this in one excel sheet
y 10.94.44.185/
w 10.94.44.184/10.94.44.185/
y 10.94.44.181/10.94.44.182
w 10.94.44.184/10.94.44.185/10.94.44.186
I want Y & W = highest details in second column
Y=10.94.44.181/10.94.44.182
W=10.94.44.184/10.94.44.185/10.94.44.186
which formula can I apply, please let me know some one.
Hi I'm trying to do a vlookup from detail related to a person ID. However, the ID has two records. One record is "Completed" and another record is "Pending Update". How do I formulate it such that the vlookup will pick up the "Pending Update" line instead of the "Completed"
I have a vlookup table that retrieves an employee list.
What I need is when I select an employee from the lookup.
When I move to the next row I only want to be able to select an employee that has not been previously selected.
To add to the above I need the function to select multiple employees.
Hi,
I have a back dated product wise period wise amendments of rates and I want to vlookup the new rate against the product for a specific date in the past.
Oscar, I know this is an older thread, but I am trying to use a version of the VLOOKUP and two conditions (date range)formula, but my table is on another tab and instead of matching my Id's from tab1 to the Ids in table 1,(C2 in the actual formula), it's providing the actual value in my table matching the cell reference from tab 1. So it's giving me the ID in cell A129 instead of trying to find a match for that ID in the table.
I am not sure what I am doing wrong. Can you provide assistance?
Tasha
Can you upload a workbook, I have trouble understanding.
[โฆ] Using multiple conditions in VLOOKUP [โฆ]