VLOOKUP 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?
Answer:
Array formula in B17 using named ranges:
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.
Get Excel *.xlsx file
vlookup of three columns to pull a single record.xlsx
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to use VLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
Return multiple values if in range
The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
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: […]
10 Responses to “VLOOKUP of three columns to pull a single record”
Leave a Reply to Deep
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.. :-)