Results 1 to 8 of 8

Thread: Vlookup with multiple criteria

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0

    Question Vlookup with multiple criteria

    Hi All,

    I am trying to look for a way to VLOOKUP with two value to get a precised match.I tried to search on Web and found that many different way to do similar things, but Im not quite understand the theory behind it.

    I have a raw data table like below:
    Parts Number Qty ID
    12345 1 XYZ
    12346 2 ABC
    12346 1 XYZ
    12345 2 ABC

    When I ordered the parts for each order, I have a specific ID for it, then once when I received the parts, I would like to know which ID it belongs to, in the beginning with not much data, I can easily to distinguish it, but when I have too much data, I found that the parts number may have many line and I am not sure which one it is, so I am thinking to VLOOKUP one more value which is the Qty to help me get an exact match. I saw the VLOOKUP+SUMPRODUCT on web for other similar case, and COUNTIF, I really felt confused....
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Try

    Define names


    LastRow =MATCH("zzzzzzzzz",Sheet1!$M:$M)
    PartsNo =Sheet1!$M$2:INDEX(Sheet1!$M:$M,LastRow)
    Qty =Sheet1!$N$2:INDEX(Sheet1!$N:$N,LastRow)
    WIP_No =Sheet1!$O$2:INDEX(Sheet1!$O:$O,LastRow)


    In T3 and copied down,

    =IFERROR(INDEX(WIP_No,MATCH(TRUE,INDEX(PartsNo&"|" &Qty=R3&"|"&S3,0,0),0)),"Not found")
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by patsir View Post
    Hi All,

    I am trying to look for a way to VLOOKUP with two value to get a precised match.I tried to search on Web and found that many different way to do similar things, but Im not quite understand the theory behind it.

    I have a raw data table like below:
    Parts Number Qty ID
    12345 1 XYZ
    12346 2 ABC
    12346 1 XYZ
    12345 2 ABC

    When I ordered the parts for each order, I have a specific ID for it, then once when I received the parts, I would like to know which ID it belongs to, in the beginning with not much data, I can easily to distinguish it, but when I have too much data, I found that the parts number may have many line and I am not sure which one it is, so I am thinking to VLOOKUP one more value which is the Qty to help me get an exact match. I saw the VLOOKUP+SUMPRODUCT on web for other similar case, and COUNTIF, I really felt confused....
    It appears that you want the WIP No. as your result, but in looking at your data, the matching value for first item has a blank for its WIP No., so what did you want returned from the formula in that case? Also, what if there are two or more rows that match your search criteria... what did you want returned in that case?

  4. #4
    Junior Member
    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Hi Rick,

    Actually the WIP no. is one of the possible result that I might be looking for. Each line will provide a parts number, qty and a order number (although this order number is only a 4 digit like AV3T and AV3U etc.,)

    It supposed that it is not allowed to have blank in WIP no, but from my understanding, it is potentially if I ordered the same parts number in same qty at the same order. Then when the goods received, I might have two identical line to fit into my list that I need to find the another way to solve it.

    In my personal thinking, I might need to use three criteria to search on my list in the future, now I hope I can understand the theory and do it by myself.

    Thanks Admin,

    But I tried to copy the formula you teach me, Im not sure are you mean I have to input "zzzzzzzzz" at the last row?

  5. #5
    Junior Member
    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    I think this is the most similar case to my case,

    http://www.mrexcel.com/forum/excel-q...-criteria.html

    {=INDEX(Sheet1!A1:A1000,MATCH(1,(Sheet1!B1:B1000=$ A2)*(Sheet1!C1:C1000=$B2)*(Sheet1!D1:D1000=$C2),0) )}

    But I do not understand what {}this mean??

  6. #6
    Junior Member
    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Actually I am planning to use the Parts No, Qty and the Air Order no. as the criteria to find out what are the WIP no., it is because based on these three criteria, I think it should be enough to find the closest match. Please comment.
    Attached Files Attached Files

  7. #7
    Junior Member
    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Finally I figure out how to use this formula ({=INDEX(Sheet1!A1:A1000,MATCH(1,(Sheet1!B1:B1000= $ A2)*(Sheet1!C1:C1000=$B2)*(Sheet1!D11000=$C2),0))}

    But if I am not matching but Vlook the WIP no., how should I do please?

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    To define dynamic ranges,

    hit CTRL + F3,

    Click on New > In the name box type LastRow and in the refers to box type the corresponding formula > OK.

    repeat the steps for PasrtsNo, Qty and WIP_No.

    Now put the formula in T3 and copied down,

    =IFERROR(INDEX(WIP_No,MATCH(TRUE,INDEX(PartsNo&"|" &Qty=R3&"|"&S3,0,0),0)),"Not found")
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Vlookup to Return Multiple Values
    By Admin in forum Download Center
    Replies: 9
    Last Post: 02-17-2017, 07:03 PM
  2. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  3. Vlookup - Multiple Sheets
    By Suhail in forum Excel Help
    Replies: 3
    Last Post: 01-30-2013, 06:47 PM
  4. Replies: 4
    Last Post: 08-11-2012, 10:50 PM
  5. VLOOKUP with Multiple Results
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 05-26-2011, 10:29 PM

Tags for this Thread

Posting Permissions

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