Lookup problem

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

Try this array formula

Code:

---

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

---

Great stuff. Thankx.
I must still master index/match functions.

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

Certainly is, it is the cover of an early Wedding Present album.

Quote:

---

Originally Posted by xld

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?

You a Weddoes fan too then?

Quote:

---

Originally Posted by xld

You a Weddoes fan too then?

---

I just had to Google that since I thought it was 'VBA for Wedding Present' :)

I will have to check that out!