Nested VLOOKUP

[ruchie]

I have to search and put values in one of my sheets after looking in
a
field of a field. I believe i need to to a nested vlookup, but im not
sure how to go about it. I copied the relevant portions of the data
below:

Department PriceType Monthly
Average


NY
NY Average $1,000.00
NJ
NJ
NJ
NJ
Average $1,500.35


I have to search within a department (for example NJ), and then
search
where average is occuring in the Price Type field, and then pick the
corresponding Monthly average value(1500.35 in this case) and put it
in the vlookup formula origination sheet.
Any help?

 

[Max]

One shot at this ..

Assuming source data as posted is in a sheet: X, data from row2 down
with col A = Dept, col B = Pricetype, col C = Monthly av

In your other sheet,

Assume depts (NY, NJ, etc) are listed in A2 down,
Put this in B2, then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=INDEX(X!$C$2:$C$100,MATCH(1,(X!$A$2:$A$100=A2)*(X!$B$2:$B$100="Average"),0))
Copy B2 down to return the required figs from col C in X. Adapt the ranges
to suit.

 

[ruchie]

thanks max! it worked perfectly fine! ur the best!

 

[Max]

welcome. good to hear that.

 

[ruchie]

max,
i ran into problem with the formula
is suddenly stopped working after i changed the data set a bit
ive been using this formula:

=INDEX(X!D3106,MATCH(1,(X!A3:A106=$A5)*(X!B3:B106="Flat/Tier/Step
Average"),0))

Could you tell me what the problem could be?

 

[Peo Sjoblom]

What do you mean by "stopped working"? I can't see anything there that would
make it stop working. Did you array enter it?

 

[ruchie]

yup i did!!! it just stopped for no reason!
let me give some background of X sheet...
the data in X are basically links from a pivot table which i created.
since index/match wasnt able to search from the pivot table earlier,
thats why i made a new sheet called X whereby i did a paste special->
paste links to it.
it was working fine uptil today, when i altered the pivot table's
source data a bit, and refreshed it. even after i changed the data on
the X file to reflect the new and current information, and edited the
formula like you see above, its showing me the #N/A result. why is
that hapenning?

 

[ruchie]

the problem is with the Match function. I just looked into the
formula's calculations. just dont know why the error is coming though..

 

[Peo Sjoblom]

It's because it cannot find a match, you might want to use something like

=IF(ISNA(MATCH(....),"",INDEX(...,MATCH(....)))

which will return a blank if no match is found