## Use multiple conditions in Vlookup

**Table of contents**

VLOOKUP - Select a column with a drop down list

VLOOKUP and two conditions (date range)

### VLOOKUP - Introduction

The first example explains how the Vlookup function works.

VLOOKUP(** lookup_value**,

**,**

*table_array***,**

*col_index_num***)**

*range_lookup*The Vlookup function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.

** lookup_value** - is the value to be found in the first column of the table. This is one of the downsides using vlookup. I will show you how to look for a value in any column using INDEX and MATCH later in this post.

** table_array** - is a table of text, numbers or logical values. Table_array can be a cell reference or a range name. By modifying the table_array using an array formula we can use multiple conditions to create a "filtered" table_array. You can also use a table name, I´ll show you this later in this post.

** col_index_num** - is the column number in table_Array from which the matching value schould be returned.

** range_lookup - **is a logical value: Find the closest match in first column sorted in ascending order (TRUE) or find an exact match (FALSE)

Another downside with VLOOKUP is that it can only return the first value even if there are multiple matches, I made post a few years ago about this:

How to return multiple values using vlookup in excel

VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

=VLOOKUP(C2, A8:E17, 3, FALSE)

This formula matches the value in cell C2 ("A") to the first column in cell range A8:E17. The second argument is the column number and the function returns the value from that column and in the same row. The last argument FALSE instructs the formula to look for an exact match.

=VLOOKUP(C2, A8:E17, 3, FALSE)

becomes

=VLOOKUP("A", {"A", "Australia", "Atlantic Corporation", "Birdie", "Schneider";"B", "North America", "Uplink Corporation", "Amshula", "Canty";"C", "Asia", "Omni Consumer Products", "Jaycee", "Martin";"D", "Europe", "Galaxy Corp", "Tracy", "Tuck";"A", "South America", "The New Firm", "Tory", "Byrnes";"B", "Australia", "ZiffCorp", "Santos", "Cowart";"C", "North America", "Minuteman Cafe", "Gen", "Lindgren";"D", "Asia", "Demo Company", "Edwin", "Shinn";"E", "Europe", "Western Gas & Electric", "Allayna", "Egan";"F", "South America", "Trans United Airways", "Ervin", "Hennessey"}, 3, FALSE)

and returns Atlantic Corporation.

### VLOOKUP and a condition

The animated picture above shows you how the array formula removes specific records in the the table_array argument.

Array formula in C4:

=VLOOKUP(C2,IF(B8:B17=C3,A8:E17,""),3,FALSE)

becomes

=VLOOKUP("A",{"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"A", "South America", "The New Firm", "Tory", "Byrnes";"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"F", "South America", "Trans United Airways", "Ervin", "Hennessey"},3,FALSE)

and returns "The New Firm"

**How to create an array formula**

- Select cell C4
- Type the array formula
- Press and hold Ctrl + Shift
- Press Enter

### 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 acell 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

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

**Array formula in cell C4:**

=VLOOKUP(C2, IF(Table2[Region]=C3, Table2, ""), MATCH(B4, Table2[#Headers], 0), FALSE)

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:**

=VLOOKUP(C2,IF((Table3[Date]>=C3)*(Table3[Date]<=C4),Table3,""),3,FALSE)

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 2012-01-02 and 2012-01-07.

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:

=INDEX(Table4[Item],MATCH(C2,Table4[First Name],0))

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

### Functions in this blog article

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MATCH(**lookup_value, lookup_array, [match_type]

Returns the relative position of an item in an array that matches a specified value

**IF(**logical_test, [value_if:true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

### 24 Responses to “Use multiple conditions in Vlookup”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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:

http://www.get-digital-help.com/2012/11/09/analyze-trends-using-pivot-tables/

You can also use formulas. How to extract a unique distinct list:

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

How to use the sumif function:

http://www.get-digital-help.com/2009/11/16/sum-values-between-two-dates-with-criteria-in-excel/

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: http://exceluser.com/blog/1107/why-index-match-is-far-better-than-vlookup-or-hlookup-in-excel.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 7-eleven

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. http://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.