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?

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:C 10))


--
Hope that helps.

Vergel Adriano


"ruchie" wrote:

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?

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:C 10))


--
Hope that helps.

Vergel Adriano


"ruchie" wrote:

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?

On Jun 8, 5:36 pm, Vergel Adriano
wrote:
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:C 10))

--
Hope that helps.

Vergel Adriano



"ruchie" wrote:
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?- Hide quoted text -

- 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"

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

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="Ave rage")*(Sheet2!C1:C10))


--
Hope that helps.

Vergel Adriano


"ruchie" wrote:

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

Hi,

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


--
Hope that helps.

Vergel Adriano


"pchr" wrote:

On Jun 8, 5:36 pm, Vergel Adriano
wrote:
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:C 10))

--
Hope that helps.

Vergel Adriano



"ruchie" wrote:
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?- Hide quoted text -

- 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"

works just fine! thanks a lot!

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