Forum

Notifications
Clear all

Finding a Price Break (PC / Excel Office 356 Version 2201)

2 Posts
2 Users
0 Reactions
101 Views
(@swyatt)
Posts: 1
New Member
Topic starter
 

I am working on our product inventory that is pulled from our ERP system (JobBOSS).  It pulls out all product information into a spreadsheet.  The information on sheet 1 is like such:

Material Number  Material Description  On Hand QTY  Status   Class      Location
        XYZ-1234                  Tube 1                    5                Active    Stock1   SPECSTOCK
        ABC-5798                  Tube 3                   10               Active    Stock1   SPECSTOCK

On sheet 2 I have all of those Material Numbers and their quantity breaks:

Material Number  Minimum Qty  Sell Price
  XYZ-1234                        5            $20.00
  XYZ-1234                       10           $15.00
  XYZ-1234                       20           $10.00
  ABC-5798                        5            $75.00
  ABC-5798                       10           $55.00
  ABC-5798                       20           $35.00

Sheet 1 is the inventory information from the ERP system and sheet 2 is the price break information for our material numbers.  What I am trying to do is pull the price break from sheet 2 based on the quantity on sheet 1.  So product XYZ-1234 has 5 pieces on hand, and I need to return the price for 5 pieces ($20.00) in a cell.

I have a full spreadsheet of all on-hand materials and one for all materials in our systems.

Any assistance is appreciated as trying to find the right wording for a google search is more difficult than turning right in a Nascar race.  🙂

 
Posted : 02/03/2022 6:01 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Shannon,

Welcome to our forum! You can use VLOOKUP with an approximate match for this. Or use Power Query to do the equivalent of a VLOOKUP approximate match. Or even the new XLOOKUP (see example 10) if you have Excel 2021 or 365.

Mynda

 
Posted : 02/03/2022 8:09 pm
Share: