Lookup 3 different items & return the value that they match

Helen · Mar 5th, 2002, 09:07 AM

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

Mar 5th, 2002, 10:17 AM

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 · Mar 5th, 2002, 03:04 PM

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 · Mar 5th, 2002, 03:25 PM

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

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 09:07 AM	#1
Helen Board Regular Join Date: Feb 2002 Posts: 103	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

Mar 5th, 2002, 10:17 AM	#2
Guest Posts: n/a	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

Mar 5th, 2002, 03:04 PM	#3
Helen Board Regular Join Date: Feb 2002 Posts: 103	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