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