Table of contents

  1. VLOOKUP and a condition
  2. VLOOKUP and a table
  3. VLOOKUP - Select a column with a drop down list
  4. VLOOKUP and two conditions (date range)
  5. INDEX and MATCH

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

  1. Select cell C4
  2. Type the array formula
  3. Press and hold Ctrl + Shift
  4. 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)

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

  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

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)

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

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