Explanation

In this example, the goal is to use VLOOKUP to retrieve the price for a given item based on three criteria: name, size, and color, which are entered in H5:H7. For example, for a Blue Medium T-shirt, VLOOKUP should return $16.00.

The VLOOKUP function does not handle multiple criteria natively. Normally VLOOKUP looks through the leftmost column in a table for a match, and returns a value from a given column in a matching row. There is no built-in way to supply multiple criteria.

This example works around this limitation by using Boolean logic to create an array of ones and zeros that represent rows that meet multiple conditions, then using this array to create a new table to provide to VLOOKUP. The overall process looks like this:

  • Use Boolean logic to test Item, Size, and Color
  • Create a new table with the CHOOSE function
  • Provide the new table to VLOOKUP
  • Configure VLOOKUP to look for 1 in the new table

This is a flexible way to apply multiple criteria with the VLOOKUP function. The logic can be extended as needed to apply more conditions, and each condition can use Excel’s full range of formula logic.

Note: This example shows an advanced technique to handle multiple criteria with VLOOKUP. If you have more basic needs, this formula takes a simple approach with a helper column. Other more flexible options include INDEX and MATCH and XLOOKUP .

Background study

This article assumes you are familiar with the VLOOKUP function and Excel Tables. If not, see:

  • Excel Tables - introduction and overview
  • VLOOKUP function - overview with examples
  • Boolean algebra in Excel - 3 minute video

Boolean algebra for criteria

Working from the inside-out, the snippet below uses Boolean logic to create a temporary array of ones and zeros:

(H5=data[Item])*(H6=data[Size])*(H7=data[Color])

Here we compare the item in H5 against all items, the size in H6 against all sizes, and the color in H7 against all colors. The initial result is three arrays of TRUE/FALSE values like this:

={FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}*{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

The math operation of multiplying the arrays together converts the TRUE FALSE values to 1s and 0s:

={0;0;0;0;1;1;1;1;0;0;0}*{0;0;1;0;0;0;1;0;0;0;1}*{1;0;1;0;0;0;1;0;0;1;0}

And after multiplication, we have a single array like this:

{0;0;0;0;0;0;1;0;0;0;0}

The process described above can be visualized as seen below. The “Result” array shows that the 7th row in the table meets all three conditions.

Multiple criteria boolean array visualization - 1

In the next step, we’ll use the Result array to build a new table that we can use with VLOOKUP.

Creating a new table

We now have an array of TRUE and FALSE values that will work as a key to which row(s) in the table meet criteria. The problem is that this array is not actually part of the table VLOOKUP needs as the table_array argument. What we need is a new table, that combines the Result array from the Boolean operation above with the Price column of the table. We can do this with the CHOOSE function.

Normally, the CHOOSE function is used to select a value by numeric position. For example, to get the second value from a list of three values, you could use CHOOSE like this:

=CHOOSE(2,"red","blue","green") // returns "blue"

Notice the index_num argument is provided as 2 to get the second value. CHOOSE is flexible, and the values it accepts can be a mix of constants, cell references, arrays, and ranges. For this problem, we need to give CHOOSE two arrays: the Boolean result array, and the Price column of the table. Then, for index_num , we provide the array constant {1,2} like this:

CHOOSE({1,2},(H5=data[Item])*(H6=data[Size])*(H7=data[Color]),data[Price])

The array constant is the tricky part. By using {1,2} for index_num we are requesting the first and second value at the same time. The CHOOSE function dutifully complies, and returns both arrays “glued” together in a single array that looks like this:

{0,15;0,16;0,17;0,17.5;0,28;0,29;1,30;0,32;0,20;0,21;0,22}

In the above format, it is hard to see the structure of the array. However, if we place the array in an Excel worksheet, the structure becomes clear. As you can see, the array is a 2-column table:

Table created by CHOOSE in memory - 2

We now have a new table we can use in VLOOKUP.

VLOOKUP function

All of the work done so far has just one purpose: to create a new table that can be used in VLOOKUP as the table_array argument. Now we need to configure the VLOOKUP function. We start by providing a lookup value of 1, to match the structure of the new table:

=VLOOKUP(1

Next, we drop in the code explained above for table_array :

=VLOOKUP(1,CHOOSE({1,2},(H5=data[Item])*(H6=data[Size])*(H7=data[Color]),data[Price])

To wrap things up, we set col_index_num to 2, and range_lookup to 0. The final formula in H8 is:

=VLOOKUP(1,CHOOSE({1,2},(H5=data[Item])*(H6=data[Size])*(H7=data[Color]),data[Price]),2,0)

VLOOKUP matches the 1 in row 7, and returns 30 as a final result. If any of the input values in H5:H7 change, a new table is assembled and VLOOKUP returns a new result.

Note: This is an array formula , and must be entered with control + shift + enter, except in Excel 365 .

Explanation

In this example, the goal is to configure VLOOKUP to perform a lookup in a table where the first column contains numbers entered as text, and the lookup value is a true number. This mismatch between numbers and text will cause VLOOKUP to return an #N/A error.

Typically, the lookup column in the table contains values that look like numbers , but are in fact numbers entered as text . When a true number is passed into VLOOKUP as the lookup_value , VLOOKUP returns #N/A, even though there appears to be a match. The screen below shows an example of this problem:

Example of VLOOKUP #N/A error caused by numbers and text mismatch - 3

The cause of the #N/A errors is that the numbers in column B are actually text, so a lookup value of 5 fails, even though it seems like VLOOKUP should match cell B9 and return information about Jupiter. The formulas in H5:H7 are as follows:

=VLOOKUP(id,planets,2,0) // name
=VLOOKUP(id,planets,3,0) // diameter
=VLOOKUP(id,planets,4,0) // satellites

All of these formulas return #N/A since they are all affected by the mismatch. One quick solution to the problem is to enter the lookup value in id (H4) as text instead of a number. You can do this by prefacing the number with a single quote (’). VLOOKUP will then correctly find the table and perform the lookup. A better solution is to make sure the lookup values in the table are indeed numbers. Once you have converted the first column to numeric values, the standard VLOOKUP formulas above will work.

However, if you don’t have control over the table, you can modify the VLOOKUP formula itself to change the numeric lookup value to text in order to match the lookup table. You can do this by concatenating an empty string ("") to the lookup value inside VLOOKUP. The formulas below show what this looks like. The first formula is a standard VLOOKUP, the second formula shows the modified version:

=VLOOKUP(id,planets,2,0)    // original
=VLOOKUP(id&"",planets,2,0) // modified

The modified version will take care of the error:

Revised VLOOKUP formula to fix error - 4

If you aren’t sure if the first column in the lookup table is text or numbers, use the ISTEXT function or the ISNUMBER function to test the values.

Both numbers and text

If you can’t be certain when you’ll have numbers and when you’ll have text, you can cater to both by wrapping VLOOKUP in the IFERROR function and using a formula that handles both cases:

=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))

Here, we first try a normal VLOOKUP formula that assumes both lookup value and the first column in the tables are numbers. If that throws an error, we try again with the revised formula above. If that formula also fails, VLOOKUP will return an #N/A error as always.