pourin up

chaserr 

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.

chaserr 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