Article updated on November 29, 2017

 

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:

=VLOOKUP(D14,IF(C3:C12=D15,B3:F12,""),3,FALSE)

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)

  1. Select cell range A7:E17
  2. Go to tab "Insert"
  3. Click table button
  4. Enable "My table has headers"
  5. 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.

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

  1. Select cell B4
  2. Go to "Data" tab
  3. Click "Data Validation" button
  4. Select "List" i the drop down list
  5. Select source: =$C$8:$E$8
  6. 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 work-around:

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 […]

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)

and returns "The New Firm"

VLOOKUP and two conditions (date range)

Array formula in cell C5:

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

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.

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)

and returns "The New Firm"

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

Learn more about the INDEX and MATCH functions:

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the MATCH function

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