I need to find a way to compare Columns A and A1 then Calculate columns B and B1 if A and A1 match. I have 2 sheets one with the the qty or parts needed to build Unit a. And sheet 2 with the actual number parts on hand. I am trying to calculate how many units can be built based on available parts. Sheet 1 - Qty needed to build unit

Part(A) -- Qty(B)

a --------- 5

b --------- 3Sheet 2 - Qty on hand

Part(A1) -- Qty(B1)

b ----------- 6

a ----------- 12I am trying to calculate how many units can be built based on available parts. (Example: For part a i can build 2 units (12/5). For part B I can build 2 units, etc). Is there a way to do this?

Try this: With a set up like this, put the formula below in Sheet2!C2 and drag it down.

VLOOKUP will find the value from Sheet 2 Column A in Sheet 1 Column A and return the value from Sheet 1 Column B.

The formula will then divide the value in Sheet 2 Column B by the value returned by the VLOOKUP and finally use the INT function to return the integer portion so you'll always get a whole number.

Sheet 1A B 1 Part(A) Qty(B) 2 a 5 3 b 12

Sheet 2:A B C 1 Part(A1) Qty(B1) Full Units 2 b 6 =INT(B2/VLOOKUP(A2,Sheet1!$A$2:$B$3,2,0)) 3 a 12

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll