Vlookup IndexMatch Countif Sumifs Back
Logo image

Vlookup with 2 Criteria using Array


A standard Vlookup() has formula:

vlookup( lookup_value, multi_cell_range, index_column_num, true as approx match in "lookup_value" ).

It searches only by 1 criteria and only matches the first occurrence but in many cases, you will need to work with datasets that contain duplicates.

Let's look at the example below and say we need to find Pear at Floor that gives us the value 8.


So in cell D3:
=VLOOKUP("Pear", IF(B3:B6="Floor", A3:C6, ""), 3, TRUE)

Then in cell D3, press Ctrl + Shift + Enter (All at once) & excel will add curly brackets to it.


What's happening here:

We are effectively turning If() into boolean array for comparison.

VLOOKUP( "Pear", IF( B3:B6="Floor", A3:C6, "" ), 3, TRUE)

Inside array IF(): IF( ( "Store"; "Store"; "Floor"; "Floor" ) = "Floor", A5:C10, "" )

Becomes: IF( ( "False"; "False"; "True"; "True" ), A5:C10, "" )

Adding range A3:C6: IF( ( "False"; "False"; "True"; "True" ),

( "Apple", "Store", "4";

"Pear", "Store", "5";

"Apple", "Floor", "5";

"Pear", "Floor", "8"; "" ) )

Vlookup boolean array now finds "Pear" in array that matches "Floor" then looks up column 3 to get value 8:

VLOOKUP( "Pear",

( "", "", "";

""; "", "";

"Apple", "Floor", "5";

"Pear", "Floor", "8"), 3, True )"