• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pricing Model: Find price based on 2 non-unique size ranges

M

MHudson

New Member
2 sheets, the 1st has a fixed price table with 3 columns: Gauge Range, Width Range, and Price. The 2nd sheet has data with 3 columns: Gauge and Width (exact numbers, not ranges). Taking the gauge and width for an item from the data sheet, I need to look up the price from the ranges in the price table.


The problem: There are four gauge ranges (0 - 0.5, 0.6 - 1.0, 1.1 - 1.5, 1.6 - 2.0) and four width ranges (0-10, 11-20, 21-30, 31-40). For each width range, all four gauge ranges have prices. So widths of 0-10 there are four prices, one for each gauge range, and so on.


So from the data sheet, how would you look up the price for a specific measurement? Let's say a gauge of 0.4 and width of 12?
 
MHudson


Is there any chance you can post a sample file of the table involved??

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Instead of having ranges, simply use the lower end of each range for the Gauge and Width values

Then use an Index/Match formula like


=Index(Data Range, Match(Gauge Value, Gauge List, 1), Match(Width Value, Width List, 1))


Refer: https://www.dropbox.com/s/xtdozi1s6pnt8hg/Gauge%20%26%20Width.xlsx
 
Hui, you sir are truly a master. FWIW, I was convinced it had to be solved by Index/Match with the mins of each range...but I was stuck thinking in a "2 dimensional" table state of mind. 3D is the answer! I'll pivot my price table and put the gauge mins in rows and width mins in columns then use your formula in my data sheet...problem solved, now I can get some sleep!


Thanks a ton Hui!!!
 
You must log in or register to reply here.
Back
Top