Author: Oscar Cronquist Article last updated on August 29, 2022

Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record?

1. VLOOKUP of three columns to pull a single record

The VLOOKUP is designed to get a value in a specified column, based on a lookup value. It can't evaluate multiple conditions and also return multiple values from the same row where the lookup value is found.

The formula below demonstrates a formula that is able to do this, read section2, and 3 below if you are using Excel 365. Those formulas are much easier to create and understand.

Array formula in B18:

=INDEX($B$3:$F$11,MATCH(1, COUNTIFS($B$14, $B$3:$B$11, $C$14, $D$3:$D$11, $D$14, $E$3:$E$11), 0), COLUMNS($A$1:A1))

Update! The VLOOKUP can process multiple conditions in some cases: How to use VLOOKUP/XLOOKUP with multiple conditions and return multiple values, here is how:

Array formula in B18:

=VLOOKUP($B$14, IF(($C$14=$D$3:$D$11)*($E$3:$E$11=$D$14), $B$3:$F$11, ""), COLUMNS($A$1:A1), 0)

Copy cell B18 and paste to cells to the right as far as needed.

How to enter an array formula

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula

Step 1 - Return 1 if all conditions are met

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

Function syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

COUNTIFS($B$14, $B$3:$B$11, $C$14, $D$3:$D$11, $D$14, $E$3:$E$11)

becomes

COUNTIFS("Y",{"X"; "Y"; "X"; "X"; "Y"; "Y"; "Y"; "X"; "Y"},"Green",{"Yellow"; "Brown"; "Blue"; "Red"; "Green"; "Pink"; "Blue"; "Yellow"; "Brown"},"TY",{"AX"; "HG"; "LM"; "WR"; "TY"; "FD"; "BV"; "MN"; "SA"})

and returns

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

Step 2 - Find the relative position

The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(1, COUNTIFS($B$14, $B$3:$B$11, $C$14, $D$3:$D$11, $D$14, $E$3:$E$11), 0)

becomes

MATCH(1, {0; 0; 0; 0; 1; 0; 0; 0; 0}, 0)

and returns 5.

Step 3 - Create a sequence of numbers from 1 to n

The COLUMNS function calculates the number of columns in a cell range.

Function syntax: COLUMNS(array)

COLUMNS($A$1:A1)

returns 1.

When cell B18 is copied to cell C18 the formula changes to COLUMNS($A$1:B1) and returns 2. The number grows by 1 for each cell.

Step 4 - Get value

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX($B$3:$F$11,MATCH(1, COUNTIFS($B$14, $B$3:$B$11, $C$14, $D$3:$D$11, $D$14, $E$3:$E$11), 0), COLUMNS($A$1:A1))

becomes

INDEX({"X","North America","Yellow","AX",2440; "Y","Africa","Brown","HG",9660; "X","Australia","Blue","LM",6190; "X","Europe","Red","WR",4980; "Y","Asia","Green","TY",8290; "Y","South America","Pink","FD",3060; "Y","Australia","Blue","BV",4890; "X","Africa","Yellow","MN",2080; "Y","North America","Brown","SA",7810},5, 1)

and returns "Y".

Back to top

Get Excel *.xlsx file

vlookup of three columns to pull a single record.xlsx

2. XLOOKUP of three columns to pull a single record

This example shows how easy it is to use the XLOOKUP function, the conditions are in cells B14, C14, and D14 respectively. The XLOOKUP function returns the record and spills values to the right as far as needed.

Excel 365 formula in cell B18:

=XLOOKUP(1,(B3:B11=B14)*(D3:D11=C14)*(E3:E11=D14),B3:F11)

Explaining formula

Step 1 - First condition

The equal sign is a logical operator, it lets you compare value to value in an Excel formula. It also works with arrays, the result is an array of boolean values TRUE and FALSE.

The first condition is specified in cell B14, it returns TRUE if a match is found.

B3:B11=B14

becomes

{"X"; "Y"; "X"; "X"; "Y"; "Y"; "Y"; "X"; "Y"}="Y"

and returns

{FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE}.

Step 2 - Second condition

The second condition is specified in cell C14, it is compared to all values in cells D3:D11.

D3:D11=C14

becomes

{"Yellow"; "Brown"; "Blue"; "Red"; "Green"; "Pink"; "Blue"; "Yellow"; "Brown"}="Green"

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}.

Step 3 - Third condition

The third condition is specified in cell D14, the value is compared to all values in cells D3:D11.

E3:E11=D14

becomes

{"AX";"HG";"LM";"WR";"TY";"FD";"BV";"MN";"SA"}="TY"

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}.

Step 4 - Control order of operation and the perform AND logic

The parentheses lets you control the order of operation, it is important that the comparisons are performed before multiplying the arrays.

(B3:B11=B18)*(D3:D11=C14)*(E3:E11=D14)

becomes

({FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})

The asterisk character lets you multiply numbers and boolean values in an Excel formula. Boolean values are converted into their numerical equivalents. TRUE - 1 and FALSE - 0 (zero).

({FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})

and returns

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

Step 5 - Get a record based on specified conditions

The XLOOKUP function search one column for a given value, and return a corresponding value in another column from the same row.

Function syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP(1,(B3:B11=B18)*(D3:D11=C14)*(E3:E11=D14),B3:F11)

becomes

XLOOKUP(1,{0; 0; 0; 0; 1; 0; 0; 0; 0},B3:F11)

becomes

XLOOKUP(1,{0; 0; 0; 0; 1; 0; 0; 0; 0}, {"X","North America","Yellow","AX",2440; "Y","Africa","Brown","HG",9660; "X","Australia","Blue","LM",6190; "X","Europe","Red","WR",4980; "Y","Asia","Green","TY",8290; "Y","South America","Pink","FD",3060; "Y","Australia","Blue","BV",4890; "X","Africa","Yellow","MN",2080; "Y","North America","Brown","SA",7810})

and returns

{"Y", "Asia", "Green", "TY", 8290}.

Back to top

3. FILTER records based on three conditions

The FILTER function lets you extract all records that match the given conditions, this example returns one record. Only one record match all given conditions.

Excel 365 formula in cell B18:

=FILTER(B3:F11,(B3:B11=B18)*(D3:D11=C14)*(E3:E11=D14))

Explaining formula

Step 1 - First condition

The equal sign is a logical operator, it lets you compare value to value in an Excel formula. It also works with arrays, the result is an array of boolean values TRUE and FALSE.

The first condition is specified in cell B14, it returns TRUE if a match is found.

B3:B11=B14

becomes

{"X"; "Y"; "X"; "X"; "Y"; "Y"; "Y"; "X"; "Y"}="Y"

and returns

{FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE}.

Step 2 - Second condition

The second condition is specified in cell C14, it is compared to all values in cells D3:D11.

D3:D11=C14

becomes

{"Yellow"; "Brown"; "Blue"; "Red"; "Green"; "Pink"; "Blue"; "Yellow"; "Brown"}="Green"

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}.

Step 3 - Third condition

The third condition is specified in cell D14, the value is compared to all values in cells D3:D11.

E3:E11=D14

becomes

{"AX";"HG";"LM";"WR";"TY";"FD";"BV";"MN";"SA"}="TY"

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}.

Step 4 - Control order of operation and the perform AND logic

The parentheses lets you control the order of operation, it is important that the comparisons are performed before multiplying the arrays.

(B3:B11=B18)*(D3:D11=C14)*(E3:E11=D14)

becomes

({FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})

The asterisk character lets you multiply numbers and boolean values in an Excel formula. Boolean values are converted into their numerical equivalents. TRUE - 1 and FALSE - 0 (zero).

({FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})*({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE})

and returns

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

Step 5 - Filter values based on multiple conditions

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:F11,(B3:B11=B18)*(D3:D11=C14)*(E3:E11=D14))

becomes

FILTER(B3:F11,{0; 0; 0; 0; 1; 0; 0; 0; 0})

and returns

{"Y", "Asia", "Green", "TY", 8290}.

Back to top