VLOOKUP/XLOOKUP 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).
({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
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: […]
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.
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