Vlookup with multiple criteria

[patsir]

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

[Admin]

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

[Rick Rothstein]

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?

[patsir]

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?

[patsir]

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??

[patsir]

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.

[patsir]

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?

[Admin]

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