Find value, check if match, then divide by 3rd value. Possible?

Occasional Visitor

My title makes no sense so let me elaborate.

vos2z_0-1663062211463.png

Here is one value I have. The part number "BCPKDP65" is an item we distribute, and the customer would like, as you can see, 981. With 981, I will divide by 228 as that's how many single items we can fit on a pallet, see below.

vos2z_1-1663062336842.png

Then, after that, I enter the value in the below workbook, which I then share with the others. 

vos2z_2-1663062464086.png

981 / 228 = 4.30

 

My question is, is there a formula I can use to automatically pull data from the first workbook I showed, match the product code against the code in the 2nd workbook listed, and then divide the two and return the result to the final workbook. I do this all manually at the moment and we have hundreds of products, I spend 1-2 hours every morning on this.

 

Any help is appreciated

 

2 Replies

@vos2z You need INDEX/MATCH function. Try-

=INDEX($C$2:$C$5,MATCH(E7,$A$2:$A$5,0))/I7

Or XLOOKUP() with O365. See the attached file.

=XLOOKUP(E7,A2:A7,C2:C7,0)/I7

Harun24HR_0-1663063489190.png

 

 

@vos2z 

Let's say the first sheet is named Sheet 1, with Part number in B3 and down, and Trader Quantity in F3 and down.

The second sheet is named Sheet 2, with Part number in C3 and down, and items per pallet in G3 and down.

On the third sheet, part numbers are in A2 and down. In the cell where you want the number of pallets:

 

=IFERROR(VLOOKUP(A2, 'Sheet 1'!$B$3:$F$1000, 5, FALSE)/VLOOKUP(A2, 'Sheet 2'!$C$3:$G$1000, 5, FALSE), "")

 

This can be filled down.