Update Pricing

%3CLINGO-SUB%20id%3D%22lingo-sub-2772379%22%20slang%3D%22en-US%22%3EUpdate%20Pricing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2772379%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20new%20to%20excel%20formulas.%20I%20need%20to%20replace%20old%20pricing%20with%20new%20pricing%2C%20however%2C%20I%20only%20have%20the%20manufacturer's%20product%20numbers%20as%20a%20%22link%22%20between%20pricing.%20This%20is%20a%20sample%20from%20my%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22588%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22133%22%20height%3D%2217%22%3EManufact.%20SKU%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EOld%20Price%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22136%22%3EManufact.%20SKU%3C%2FTD%3E%3CTD%20width%3D%22145%22%3ENew%20Price%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%246.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.041%3C%2FTD%3E%3CTD%3E%24%2020.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.6415.045.000%3C%2FTD%3E%3CTD%3E%2435.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.289%3C%2FTD%3E%3CTD%3E%24%2035.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%24199.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.290%3C%2FTD%3E%3CTD%3E%24%2035.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.6415.041.010%3C%2FTD%3E%3CTD%3E%24209.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.292%3C%2FTD%3E%3CTD%3E%24%2035.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.6415.045.020%3C%2FTD%3E%3CTD%3E%24199.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.293%3C%2FTD%3E%3CTD%3E%24%2017.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.6415.041.000%3C%2FTD%3E%3CTD%3E%2464.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.330%3C%2FTD%3E%3CTD%3E%24%2026.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.6415.033.000%3C%2FTD%3E%3CTD%3E%2441.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.367%3C%2FTD%3E%3CTD%3E%24%2034.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%24335.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.393%3C%2FTD%3E%3CTD%3E%24%2044.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%24154.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.394%3C%2FTD%3E%3CTD%3E%24%2044.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%2461.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.395%3C%2FTD%3E%3CTD%3E%24%2026.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.5115.048.020%3C%2FTD%3E%3CTD%3E%2472.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.0000.200.396%3C%2FTD%3E%3CTD%3E%24%2026.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.5115.048.030%3C%2FTD%3E%3CTD%3E%2472.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E72.2742.200.750%3C%2FTD%3E%3CTD%3E%24%2017.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%2499.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E72.2740.200.751%3C%2FTD%3E%3CTD%3E%24%2017.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.5115.056.000%3C%2FTD%3E%3CTD%3E%2475.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E86.2706.114.105%3C%2FTD%3E%3CTD%3E%24%2017.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.6415.035.000%3C%2FTD%3E%3CTD%3E%2441.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E87.2745.114.105%3C%2FTD%3E%3CTD%3E%24%2022.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%2429.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.1918.336.002%3C%2FTD%3E%3CTD%3E%24%200.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.6415.035.010%3C%2FTD%3E%3CTD%3E%24219.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.1918.336.003%3C%2FTD%3E%3CTD%3E%24%200.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%2474.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.1918.336.001%3C%2FTD%3E%3CTD%3E%24%200.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.7915.017.130%3C%2FTD%3E%3CTD%3E%2433.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E11.4307.250.000%3C%2FTD%3E%3CTD%3E%24%2015.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%2442.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E11.4308.848.000%3C%2FTD%3E%3CTD%3E%24%2053.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.5215.012.010%3C%2FTD%3E%3CTD%3E%2469.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E11.4308.849.000%3C%2FTD%3E%3CTD%3E%24%2080.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.7915.042.000%3C%2FTD%3E%3CTD%3E%2422.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E11.4309.207.000%3C%2FTD%3E%3CTD%3E%24%2053.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%249.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.6415.001.000%3C%2FTD%3E%3CTD%3E%24%2039.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%2433.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.7900.001.000%3C%2FTD%3E%3CTD%3E%24%2035.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E00.5018.100.001%3C%2FTD%3E%3CTD%3E%24132.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E85.2737.270.074%3C%2FTD%3E%3CTD%3E%24%2017.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%249.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E11.5115.000.000%3C%2FTD%3E%3CTD%3E%24%2018.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E200-750%3C%2FTD%3E%3CTD%3E%244.50%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E00.2415.009.000%3C%2FTD%3E%3CTD%3E%24%2035.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E200-751%3C%2FTD%3E%3CTD%3E%2415.95%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E48.2723.114.005%3C%2FTD%3E%3CTD%3E%24%2018.00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2772379%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2772694%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Pricing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2772694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1162874%22%20target%3D%22_blank%22%3E%40MrCOSTCO%3C%2FA%3E%26nbsp%3BThe%20attached%20workbook%20contains%20a%20working%20example%20based%20on%20the%20data%20you%20provided%2C%20assuming%20you%20can%20use%20XLOOKUP%20(see%20column%20C.%20Alternatively%2C%20use%20VLOOKUP%20(see%20column%20D).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20a%20few%20codes%20so%20that%20there%20would%20in%20fact%20be%20matching%20SKU's.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2775301%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Pricing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2775301%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1162874%22%20target%3D%22_blank%22%3E%40MrCOSTCO%3C%2FA%3E%26nbsp%3BOoops!%20I%20forgot%20to%20add%20some%20%24%20signs%20in%20my%20file%20when%20I%20entered%20the%20VLOOKUP%20formula.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I am new to excel formulas. I need to replace old pricing with new pricing, however, I only have the manufacturer's product numbers as a "link" between pricing. This is a sample from my sheet.

 

 

Manufact. SKUOld Price Manufact. SKUNew Price
 $6.95 00.0000.200.041$ 20.00
00.6415.045.000$35.95 00.0000.200.289$ 35.00
 $199.95 00.0000.200.290$ 35.00
00.6415.041.010$209.95 00.0000.200.292$ 35.00
00.6415.045.020$199.95 00.0000.200.293$ 17.00
00.6415.041.000$64.00 00.0000.200.330$ 26.00
00.6415.033.000$41.00 00.0000.200.367$ 34.00
 $335.95 00.0000.200.393$ 44.00
 $154.95 00.0000.200.394$ 44.00
 $61.95 00.0000.200.395$ 26.00
00.5115.048.020$72.00 00.0000.200.396$ 26.00
00.5115.048.030$72.00 72.2742.200.750$ 17.00
 $99.95 72.2740.200.751$ 17.00
00.5115.056.000$75.95 86.2706.114.105$ 17.00
00.6415.035.000$41.95 87.2745.114.105$ 22.00
 $29.95 00.1918.336.002$ 0.00
00.6415.035.010$219.95 00.1918.336.003$ 0.00
 $74.95 00.1918.336.001$ 0.00
00.7915.017.130$33.00 11.4307.250.000$ 15.00
 $42.95 11.4308.848.000$ 53.00
00.5215.012.010$69.95 11.4308.849.000$ 80.00
00.7915.042.000$22.00 11.4309.207.000$ 53.00
 $9.95 00.6415.001.000$ 39.00
 $33.00 00.7900.001.000$ 35.00
00.5018.100.001$132.95 85.2737.270.074$ 17.00
 $9.95 11.5115.000.000$ 18.00
200-750$4.50 00.2415.009.000$ 35.00
200-751$15.95 48.2723.114.005$ 18.00
4 Replies

@MrCOSTCO The attached workbook contains a working example based on the data you provided, assuming you can use XLOOKUP (see column C. Alternatively, use VLOOKUP (see column D).

 

I changed a few codes so that there would in fact be matching SKU's. 

@Riny_van_Eekelen 

Could you give this a look? I tried but not everything populated.

@MrCOSTCO Ooops! I forgot to add some $ signs in my file when I entered the VLOOKUP formula. See attached.

@MrCOSTCO 

As a variant see File

Yea_So_0-1632363241556.png