is there such a thing as a nested vlookup? 2

[gpeterman]

I'll try this without attaching a work sheet. I have a working sheet 1 and a sheet 2 where my data is imported to. I want to write a formula in a cell on sheet 1 similar to a VLookup, except it is looking for information defined by 3 separate cells on sheet 1. We are looking for an exact match in column 1 then in column 2 then in column 3 and I want to return the value found at column 4 of that row.
I can do it manually by using filters. Any bright ideas?

 

[SkipVought]

hi,

No. But you could use a concatenated key along with INDEX() & MATCH() which in my humble opinion is superior to VLOOKUP()

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Would you pleas post a sample of the data in the columns you referenced.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

[highlight #FCE94F][/highlight]
Here's something else you can do IF... there is ONLY one occurrence of the values in columns 1-3.

Example:
[pre]
name1 name2 name3 name4
skip abe
al
fred bob
[highlight #FCE94F]skip mary fred[/highlight] chas
fred carl
skip dan
mary dave
skip don
mary evan

[/pre]

Add a column that is sequentially numbered...
[pre]
name1 name2 name3 name4 row
skip abe 1
al 2
fred bob 3
skip mary fred chas 4
fred carl 5
skip dan 6
mary dave 7
skip don 8
mary evan 9
[/pre]

Then your lookup is (using Named Ranges)...
[tt]
=INDEX(name4,SUMPRODUCT((name1="skip")*(name2="mary")*(name3="fred")*(row)))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

There are probably several ways to achieve what I think you are trying to do.[ ] Take a look at the worksheet extract below.

A       B       C       D       E       F

3       Table of data:				
4		Col 1	Col 2	Col 3	Col 4
5		Apple	Blue	Many	uu
6		Peach	Red	Some	vv
7		Plum	Yellow	Few	ww
8		Banana	Blue	Few	xx
9		Plum	Yellow	Many	yy
10		Quince	Purple	Few	zz
11					
12	Values to enter table:				
13		Plum	Yellow	Few	ww

The only formula in this is in cell F13 (the one with "ww" in it), and is the ARRAY FORMULA

{=INDEX ( F5:F10 , MAX((C5:C10=$C$13)*(D5:D10=$D$13)*(E5:E10=$E$13)*(ROW(F5:F10)-ROW($F$5)+1)) )}

where the br{ac}es are not typed, and where I have added a few spaces to make it a bit more understandable.

You might want to refine it a bit to correctly handle the case where there is no triple-match (because as given it will behave as if the match was found on the first line.[ ] Also note that the case where you have multiple lines with a triple-match will return the last of the matches, something I'll leave it to you to rectify if necessary.

 

[Deniall]

Skip and I were typing simultaneously.

 

[SkipVought]

I like Deniall's use of the ROW() function, so I modified my formula to eliminate the need for a row column...

[tt]
=INDEX(name4,SUMPRODUCT((name1=A5)*(name2=B5)*(name3=C5)*(ROW(name4)-1)))
[/tt]
where A5, B5 & C5 contain the 3 lookup values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I just posted this faq68-7708 that uses a more general approch.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mscallisto]

Nice post Skip very clever and useful.
It's a fine example for beginners to learn about the usefulness of index, sumproduct and lookups

Sam

 

[SkipVought]

Thx, Sam!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]