Lookup 3 different items & return the value that they match

[Helen]

I have a huge spreadsheet that I need to do a lookup on. I have a table of months, and I need to lookup 3 different things (e.g. a part no & the customer & the type set), and when those three match return the column reference that I specify.

Can this be done?

thanks for your help

 

[Guest]

I had a similar Problem and inserted an extra column into my table. Then concatenated the columns I wanted to look up and did a lookup (sumif?) on this concatenated column.

Hope this helps

 

[Helen]

I actually got another method to this problem which works a treat, they e-mailed me at home because they couldn't post the answer - so here it is for anyone else that could use the help:

If I understand your requirement you can do this with SUMPRODUCT.
As an example, put the part# to be looked up in B1, the customer in B2,
and the type set in B3. Then assuming your table starts in A6 with the 4
columns
enter this formula in B4:

=SUMPRODUCT((A6:A200=B1)*(B6:B200=B2)*(C6:C200=B3),D6:D200)

If your conditions are met it will give you the value in the fourth column.

CHORDially,
Art Farrell

 

[Aladin Akyurek]

Helen,

> I actually got another method to this problem which works a treat, they e-mailed me at home because they couldn't post the answer - so here it is for anyone else that could use the help:

That's kind of you, but see also the Archives for "Multiconditional Sum" and specifically

http://www.mrexcel.com/wwwboard/messages/8961.html

Aladin

On 2002-03-05 14:04, Helen wrote:
I actually got another method to this problem which works a treat, they e-mailed me at home because they couldn't post the answer - so here it is for anyone else that could use the help:

If I understand your requirement you can do this with SUMPRODUCT.
As an example, put the part# to be looked up in B1, the customer in B2,
and the type set in B3. Then assuming your table starts in A6 with the 4
columns
enter this formula in B4:

=SUMPRODUCT((A6:A200=B1)*(B6:B200=B2)*(C6:C200=B3),D6:D200)

If your conditions are met it will give you the value in the fourth column.

CHORDially,
Art Farrell