VLOOKUP issue

[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?

 

[Don Guillett]

This is an array formula that must be entered using ctrl+shift+enter vs just
enter.
where col H is your dept and col I is your average. Looking for the MAX so
if the average goes down it wouldn't be correct. Then you could use the
MATCH function to find the last entry for that Dept and INDEX for the
average.

=MAX(IF(H2:H22="a",I2:I22))

 

[ruchie]

thanks don!

 

[Don Guillett]

Hope it helped