PDA

View Full Version : Solved: Lookup problem

Bazza
09-29-2009, 03:26 AM
I have the following which I would like to do, but cant seem to get it right.

Col A ******Col B ******Col C *******Col D

John ********KZN ********1 ************2
Walt *********MP ********3 ************1
Joe **********GN ********2 *************2
Joe **********KZN *******1 *************1

(excuse asterisks, but couldnt get the items lined up under the columns:o: )

I would like to lookup an item in col A (say “Joe”) and col B (say “KZN”) – this equates
to the last row of data. However once this is “matched” I want the answer to concatenate the corresponding items
in col C and D. Hence my answer s/be 11.

I have tried to use SUMPRODUCT to return a value of “1” when it matches in col A and B, and then to return the corresponding
col C and D as the answer, but cant get that right.:doh:
There is probably an easier way for the boffins but I’m struggling.
Thankx

Bob Phillips
09-29-2009, 03:51 AM
Try this array formula

=INDEX(C:C,MATCH(1,(A1:A100="Joe")*(B1:B100="KZN"),0))&
INDEX(D:D,MATCH(1,(A1:A100="Joe")*(B1:B100="KZN"),0))

Bazza
09-29-2009, 05:27 AM
Great stuff. Thankx.
I must still master index/match functions.

PS - is ur avatar a pic of George Best. Looks like.

Bob Phillips
09-29-2009, 05:42 AM
Certainly is, it is the cover of an early Wedding Present album.

10-01-2009, 04:41 AM
Certainly is, it is the cover of an early Wedding Present album.

Nice; I've got 'Suck' playing in my iPhone right now while I'm working! Who knew?

Bob Phillips
10-01-2009, 08:24 AM
You a Weddoes fan too then?