## VLOOKUP/XLOOKUP of three columns to pull a single record

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

**Table of Contents**

## 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:

**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:

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".

### 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:

### 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}.

## 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:

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

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}.

### Vlookup category

I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]

Question: How do I search a specific data set, I have two tables to choose from? Answer: Formula in cell C13: […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 11 Responses to “VLOOKUP/XLOOKUP of three columns to pull a single record”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hi Oscar,

How do i change the font size and color in a combo box ?

Appreciate your help.

Thanks

Haroun

Haroun,

You can only change font size and color in an active x combo box.

Read more: https://www.ozgrid.com/forum/showthread.php?t=73189

hi oscar,

this above solution is very good and very handy. thank you very much. i made a slight update to this for error-suppression that i thought of sharing here:

={LOOKUP(REPT("Z",25), CHOOSE({1,2},"", INDEX(tbl, SMALL(IF(COUNTIF(search_values, INDEX(tbl, , 1, 1))+COUNTIF(search_values, INDEX(tbl, , 2, 1))+COUNTIF(search_values, INDEX(tbl, , 4, 1)), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROW(A1)), 5)))}

i do not have access to XL'03 in order to check this, but i hope that by _not_ using XL'07-specific "IFERROR" for error-suppression, this formula may be useful for older versions too. am i correct in that assumption?

the purpose for which i employed this formula, i was able to drop the 'area_num' argument from INDEX. is there a specific situation in which *not* having that would wreak havoc?

as always, much thanks and kind regards for all that you share with us.

K. Yantri

i do not have access to XL'03 in order to check this, but i hope that by _not_ using XL'07-specific "IFERROR" for error-suppression, this formula may be useful for older versions too. am i correct in that assumption?No, use =IF(ISERROR(formula), errorformula, formula)

Thanks for commenting!

Does anyone know how to match four columns to pull a single record?

Sheet 1

Description Age Sum of PWK01

Fred A =value reqquired

Mike B =value reqquired

Samuel C =value reqquired

Joshua D =value reqquired

Eric E =value reqquired

Sheet 2

Description Item Age Week 1 Week 2

Fred Kiwis A 31.802571712 37.802571712

Mike Kiwis D 20.528476326 21.528476326

Samuel Kiwis C 52.331048038 51.331048038

Joshua Kiwis F 1457907.9884 1467907.9884

Eric Kiwis E 1481550.2918 1491550.2918

Fred Kiwis B 31.802571712 37.802571712

Mike Kiwis B 20.528476326 21.528476326

Samuel Kiwis G 52.331048038 51.331048038

Joshua Kiwis D 1457907.9884 1467907.9884

Eric Kiwis I 1481550.2918 1491550.2918

Thanks Mike

Mike,

I think I can do that. But I don´t understand your data. What is the desired outcome?

Dear Oscar Sir,

I am searching for one tricky thing to accomplish using (only) formulas (and not VBA).

I will be thankful if you can help me.

The excel sheet has several columns, I want to filter data by two columns, here, column Speciality = "*Port*", and also, Testing? = "No", now the answer should be value of column "Name" for the first resulting row from the filter formula.

Excel preview data is as follows:

-------------------

Name Speciality Perma? Testng? Success?

A Oil Engine & Automobiles No Yes Yes

B Diamond & Textile Industries No Yes No

C Plastic Industries & Wine No Yes Yes

D IT & Automation No Yes Yes

E Brass Material & Port No Yes Yes

F Port & Shipping Industries No No N/A

G Tours & Spices No Yes Yes

H General No Yes No

I Tours, Divine, Port, etc No No N/A

J Tours & Fisheries No Yes Yes

K Tours & Others No Yes Yes

L Tours & Others Yes Yes Yes

M Film Industries & Hotels Yes Yes No

N Plastic & Other Industries No Yes Yes

O Tours, Wine & Port Yes Yes Yes

Name of person who has speciality matching "PORT" and is not in "Testing" version:

ANSWER = ?? FORMULA ??

Speciality = "*PORT*" + Testing? = "No"

=

[Respective Value of: Column A]

-------------------

In this case, answer should be: F

Awaiting for your reply.

Thanks & Regards,

Deep

Deep,

Array formula in cell A21:

=INDEX($A$2:$A$16, MATCH(1, ISNUMBER(SEARCH(A19, $B$2:$B$16))*($C$2:$C$16=B19), 0))

The answer should be E?

Thanks for the code. I'll check it out. (Sorry for delayed response)

:) Keep up the good work..

Yes sir!! Perfect answer.

Wow! Amazing.. 10 out of 10.. :-)

Dear Sir,

Can you help me with this formula

Project Bid Status Budget Revenue

Sales $203,00 Won $1,000 to $5,000 $5.800,00

Online $151,00 Lost $10,000 to $15,000 $31.700,00

Sales + Online $180,00 Won $5,000 to $10,000 $14.200,00

Online $173,00 Lost $10,000 to $15,000 $9.900,00

Sales $0,00 Won Below $1,000 $16.600,00

Sales + Online $151,00 Won $10,000 to $15,000 $29.400,00

Sales + Online $151,00 Won $1,000 to $5,000 $33.300,00

Online $308,00 Lost Below $1,000 $11.700,00

1. How to make Dropdown list referencing the Bid Amount

2. VLOOKUP formula to display Potential Revenue if we using the dropdown list according to the bid amount above

Thanks