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

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History