+ Reply to Thread
Results 1 to 9 of 9

Need wholesale prices to replace retail prices based on SKU number.

  1. #1
    Registered User
    Join Date
    01-25-2021
    Location
    Oregon
    MS-Off Ver
    365
    Posts
    11

    Unhappy Need wholesale prices to replace retail prices based on SKU number.

    The attached workbook has two tabs, retail and wholesale.
    The wholesale Variant Price and Variant Compare at Price need to replace the retail Variant Price and Variant Compare at Price on the retail tab. The names and stuff aren't always the same, so this needs to be done by matching the SKU numbers and taking the prices off the same line.

    I have tried a ton of solutions, and can't figure it out!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Need wholesale prices to replace retail prices based on SKU number.

    Use the Vlookup function. Here is a tutorial on the syntax.

    https://www.techonthenet.com/excel/formulas/vlookup.php
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Need wholesale prices to replace retail prices based on SKU number.

    On Sheet "Retail RO GP SS 0922", in Cell U3, try:

    =IF($O3="","",XLOOKUP($O3,Wholesale!$M:$M,Wholesale!S:S,"not found",0))

    and copy down

    Cell V3:
    =IF($O3="","",XLOOKUP($O3,Wholesale!$M:$M,Wholesale!T:T,"not found",0))

    and copy down.

  4. #4
    Registered User
    Join Date
    01-25-2021
    Location
    Oregon
    MS-Off Ver
    365
    Posts
    11

    Re: Need wholesale prices to replace retail prices based on SKU number.

    Hm, it just says "not found" in both cells.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Need wholesale prices to replace retail prices based on SKU number.

    Pl see file. ARRAY formulas are used.
    In U3

    =IFERROR(INDEX(Wholesale!$S$2:$S$2655,MATCH(O3,Wholesale!$M$2:$M$2655,0)),"")

    In V3

    =IFERROR(INDEX(Wholesale!$T$2:$T$2655,MATCH(O3,Wholesale!$M$2:$M$2655,0)),"")

    since your excel version is 365 ARRAY entry may not be required. Try this with normal entry.
    If not worked
    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 09-23-2021 at 12:35 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Need wholesale prices to replace retail prices based on SKU number.

    @KVS: Array formulas not required on simple INDEX/MATCH (post #5)

    Alternative is simple VLOOKUP

    =VLOOKUP($O3,Wholesale!$M$2:$T$2665,7,FALSE)

    =VLOOKUP($O3,Wholesale!$M$2:$T$2665,8,FALSE)
    Last edited by JohnTopley; 09-23-2021 at 03:39 AM.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Need wholesale prices to replace retail prices based on SKU number.

    Yes, it is by mistake. Normal formulas will work . ARRAY entry not required.
    Pl see file.
    In U3

    =IFERROR(INDEX(Wholesale!$S$2:$S$2655,MATCH(O3,Wholesale!$M$2:$M$2655,0)),"")

    In V3

    =IFERROR(INDEX(Wholesale!$T$2:$T$2655,MATCH(O3,Wholesale!$M$2:$M$2655,0)),"")
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Need wholesale prices to replace retail prices based on SKU number.

    You only get "not found" for those SKU's that aren't found in the Wholesale sheet (you can show nothing for those if you want by just removing the "not found" in the formula).

    The formulas I gave you are for MS365. Please see attached.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-25-2021
    Location
    Oregon
    MS-Off Ver
    365
    Posts
    11

    Thumbs up Re: Need wholesale prices to replace retail prices based on SKU number.

    Thanks for all of your work to help with this, everyone! The VLOOKUP did it, and I will mark that as the solution. You saved me even more hours of headache, thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 01-28-2014, 05:25 PM
  2. Generate xls spreadsheet showing retail prices and not wholesale ones
    By philjuk in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-05-2013, 08:23 PM
  3. [SOLVED] Extracting latest prices from a price book with past and present prices
    By benoj2005 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-10-2012, 04:08 AM
  4. Replies: 6
    Last Post: 05-01-2010, 04:37 AM
  5. Retail prices end in 9!
    By ktexcel in forum Excel General
    Replies: 4
    Last Post: 02-11-2006, 11:15 PM
  6. Use a multiplier to change List Prices to Net prices
    By Dangada in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 12:05 PM
  7. Cyclic prices into trend prices
    By atult in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-27-2005, 01:19 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1