VLookup with Quantity Comparison

[MrJerry]

I need your help on a vlookup function. I have two worksheets with two similar sets of data. I finished the vlookup to make sure one set of data includes all order numbers in the other set. I need to take it one step further. I need to check that a order quantity on one sheet matches the order quantity on another sheet for the same order number. How do I do that?

 

[Aladin Akyurek]

I need your help on a vlookup function. I have two worksheets with two similar sets of data. I finished the vlookup to make sure one set of data includes all order numbers in the other set. I need to take it one step further. I need to check that a order quantity on one sheet matches the order quantity on another sheet for the same order number. How do I do that?

On Sheet1 with A2 housing an order number...

=B2=INDEX(Sheet2!$C$2:$C200,MATCH(A2,Sheet2!$A$2:$A$200,0))

This compares the amount in B2 with the amount Index/Match finds on Sheet2 in C2:C200.

 

[MrJerry]

Thank you so much!!! It worked great!! Could you please talk through the formula so I can explain it? Thank you!!

 

[Aladin Akyurek]

Thank you so much!!! It worked great!!

You are welcome.

Could you please talk through the formula so I can explain it? Thank you!!

A] MATCH determines the position of a look up value in a reference.

B] INDEX returns a result value at the same position as the look up value from an associated reference.

Example. Consider E2:F4 below...

FAD,18
KAD,80
LAD,25

Goal: Get the value from B2:B4 that corresponds to KAD in A2:A4.

INDEX(F2:F4,MATCH("KAD",E2:E4,0))

would achieve the goal... Looked closely at this formula, we have:

MATCH("KAD",E2:E4,0)

==> 2

INDEX(F2:F4,2)

==> 80

We can compare the result of such formula with an existing value...

Let J2 house 45. If we try, for example in K2:

=J2=INDEX(F2:F4,MATCH("KAD",E2:E4,0))

we would get FALSE for 45 is not equal to 80 which Index/Match returns.

Hope this helps.