March 2, 2022
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. 🙂
July 16, 2010
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.