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?

 

[Guest]

Ruchie,

Assuming the Department, PriceType, and Monthly Average columns are in
columns A, B, and C respectively, try something like this:

=SUMPRODUCT((A1:A10="NJ")*(B1:B10="Average")*(C1:C10))

 

[Guest]

Ruchie,

Assuming the Department, PriceType, and Monthly Average columns are in
columns A, B, and C respectively, try something like this:

=SUMPRODUCT((A1:A10="NJ")*(B1:B10="Average")*(C1:C10))

 

[merjet]

Assuming your data is in cells A2:C7, put the following formula in
another column of row 2, then copy it down.
=SUMPRODUCT(--(A$2:A$7=A2),--(B$2:B$7="Average"),--(C$2:C$7))

If you need it in VBA, it's easy to convert.

Hth,
Merjet

 

[pchr]

Ruchie,

Assuming the Department, PriceType, and Monthly Average columns are in
columns A, B, and C respectively, try something like this:

=SUMPRODUCT((A1:A10="NJ")*(B1:B10="Average")*(C1:C10))

--
Hope that helps.

Vergel Adriano








- Show quoted text -

following Vergel- I think you might have to push ctrl+shift+enter (not
just enter) to enter the formula as this is an "array formula"

 

[ruchie]

actually i have to use the monthly average field to feed into another
worksheet, which is a consolidation sheet.
the consolidated worksheet lists the departments uniquely, and i have
to search the corresponding entry of department in this sheet, then
the average, then the monthly average field, all from this other
consolidation sheet. i hope i am able to explain the problem
properly...

 

[Guest]

I'm not sure I fully understand it now, but assuming your data sheet is
Sheet2 and your consolidation sheet is Sheet1.. and assuming sheet1!A2 has
"NJ", in Sheet1!B2, enter this formula:

=SUMPRODUCT((Sheet2!A1:A10=A2)*(Sheet2!B1:B10="Average")*(Sheet2!C1:C10))

 

[Guest]

Hi,

it works as a regular formula, no need to CTRL+SHIFT+ENTER...

 

[ruchie]

works just fine! thanks a lot!