VLOOKUP/XLOOKUP of three columns to pull a single record

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?

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:

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

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

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

Vlookup category

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

Use VLOOKUP to calculate discounts, commissions, tariffs, charges, shipping costs, packaging expenses or bonuses
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]

Choose between two data sets to VLOOKUP
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

INDEX MATCH COUNTIFS COLUMNS VLOOKUP IF XLOOKUP FILTER

More than 1300 Excel formulas

Excel formula categories

Average Choose Combine Merge Compare Concatenate Conditional Formatting Count Count values Dates Duplicates Extract Filter Filter records Filtered values Filterxml Hyperlink If cell Index Match Logic Lookups Match Max Min Numbers in sum OR logic Overlapping Partial match Records Sequence Small Sort by Sort values Sum Sumifs Sumproduct String manipulation Time Unique distinct records Unique distinct values Unique records Unique values Vlookup Vlookup return values

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

Haroun says:

April 3, 2012 at 8:36 pm

Hi Oscar,

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

Appreciate your help.

Thanks
Haroun

Reply
Oscar says:

April 9, 2012 at 11:34 am

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

Reply
K. Yantri says:

April 26, 2012 at 5:34 am

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.

Reply
Oscar says:

April 30, 2012 at 12:52 pm

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!

Reply
Mike says:

June 26, 2013 at 2:37 pm

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

Reply
- Oscar says:
  
  July 2, 2013 at 8:43 am
  
  Mike,
  
  I think I can do that. But I don´t understand your data. What is the desired outcome?
Deep says:

April 15, 2014 at 9:23 pm

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

Reply
- Oscar says:
  
  April 23, 2014 at 11:54 am
  
  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?
- Deep says:
  
  May 5, 2014 at 11:16 am
  
  Thanks for the code. I'll check it out. (Sorry for delayed response)
  :) Keep up the good work..
- Deep says:
  
  May 5, 2014 at 11:22 am
  
  Yes sir!! Perfect answer.
  Wow! Amazing.. 10 out of 10.. :-)
Sagio says:

December 17, 2022 at 7:01 am

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

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