Lookup 3 different items & return the value that they match
Lookup 3 different items & return the value that they match
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Lookup 3 different items & return the value that they match

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Guest

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,820
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com