I think the more common approach to your situation is:
On a separate sheet, create a listing of Products, Cost, RetailPrice
Example:
Product/Cost/Price table on Sheet2, A1:C500
Select that range, then
Type LU_ProdPrices in the Name Box (just above the Col_A heading)
Press [Enter] (That creates a named range)
Then, on your input sheet:
A1: Item
B1: Cost
C1: Price
A2: (your product code)
B2: =VLOOKUP(A2,LU_ProdPrices,2,0)
C2: =VLOOKUP(A2,LU_ProdPrices,3,0)
Copy the formulas in B2 and C2 down as far as needed.
Note: if you don't want to see errors associated with blank Items in
Col_A, use these formulas:
B2:
=IF(ISERROR(VLOOKUP(A2,LU_ProdPrices,2,0)),"",VLOO KUP(A2,LU_ProdPrices,2,0))
C2:
=IF(ISERROR(VLOOKUP(A2,LU_ProdPrices,3,0)),"",VLOO KUP(A2,LU_ProdPrices,3,0))
Does that give you something to work with?
Regards,
Ron
--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile:
http://www.excelforum.com/member.php...o&userid=21419
View this thread:
http://www.excelforum.com/showthread...hreadid=491929