Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Lookup more than one number (Excel 2002)

    Hi

    Ineed a vlookup formula to search on more than one number, if this is possible please.

    Please see attached sheet.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup more than one number (Excel 2002)

    Hi Braddy

    The attached spreadsheet shows a possible solution using the INDEX and MATCH functions in an array formula. To enter the formula you use Control+Shift+Enter

    The 2 values to search for are concatenated and this value is then used to search through an array made up of concatenated values from the lookup table. Unfortunately it is not a particularly easy or elegant solution.

    In the spreadsheet the values to lookup are in cells A2 & B2, the result in Cell C2 and the lookup table in cells E2:G8

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup more than one number (Excel 2002)

    I assume AddNum is the lookup range, and col 4 is the value to get. I will also assume that the item code is in column1 and the Cust code in COl 2 of the range.

    If the item code and Customer are each unique in their respective lists, you could do a MATCH on each and if the rows were equal indx on the "value" list to get that row. This will not work if either is not unique

    This formula could look like:
    =IF(MATCH(A2,INDEX(AddNum,,1),0)=MATCH(B2,INDEX(Ad dNum,,2),0),INDEX(AddNum,MATCH(B2,INDEX(AddNum,,2) ,0),4),"")

    If they are not unique and there is only 1 such combination this ARRAY formula should work (confirm with ctrl-shift-enter)
    =SUM(IF((INDEX(AddNum,,1)=A2)*(INDEX(AddNum,,2)=B2 ),INDEX(AddNum,,4)))

    If there are multiple combinations, the above will sum them up (you can replace the sum with other stat functions (count, average, min, max, std, etc) if desired.

    If your needs are different than outlined, you will need to elaborate and probably create a custom function.
    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lookup more than one number (Excel 2002)

    Hi Steve

    The codes are not unique they repeat several time in the sheet , for instance the Item code will appear along side several different customer codes. hence the neeed to make sure they match .

    So I will try the second formula the array ,

    Thanks to you and Tony for your reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup more than one number (Excel 2002)

    You could do a multi-key lookup by extending your lookup table (addnum) with an additional column by concatenating its first two columns...

    Let B2:E20 on Sheet2 house the lookup table with Item Code in B and Customer in C.

    In A2 enter & copy down:

    =B2&","&C2

    Now select A2:E20 and name the selection "addnum".

    This setup allows you to invoke an ordinary VLOOKUP formula...

    =VLOOKUP(A2&","&B2,addnum,5,0)

    where A2 houses an Item Code interest and B2 a customer of interest on, say, Sheet1.

    See attachment.
    Microsoft MVP - Excel

Posting Permissions

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