Consulting

Results 1 to 7 of 7

Thread: Lookup problem

  1. #1
    VBAX Regular
    Joined
    Jul 2009
    Posts
    7
    Location

    Unhappy 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 )



    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.
    There is probably an easier way for the boffins but I’m struggling.
    Thankx
    Last edited by Bazza; 09-29-2009 at 03:44 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Jul 2009
    Posts
    7
    Location
    Great stuff. Thankx.
    I must still master index/match functions.

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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Certainly is, it is the cover of an early Wedding Present album.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You a Weddoes fan too then?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •