Search Excel price grid with variable values

Question

                       WIDTH                   
DROP    1150    1550    1850    2160    2360    2750    3000    3480    3980    4450
1200    338     348     378     413     431     450     467     513     549     579
1500    343     384     408     431     455     473     490     542     570     602
1800    374     397     422     452     480     504     528     598     627     661
2100    401     425     455     483     508     531     560     619     621     684
2400    418     448     478     508     531     560     584     642     678     713

In the above matrix the width ranges in variance from the preceding value in the width from 200 to 500 units.

I need to be able to search for example a value 2500 wide (which must round up to the next highest value. In this case 2750) and 1501 Drop (which must round up to the next highest value which in this case is 1800)

My problem is that I I am unsure of which formula to use in order to find the appropriate width:

=INDEX(B3:K7,MATCH(CEILING(1501,300),A3:A7,0),?????????)

Any Ideas?

Answer 1

I've used INDIRECT and ADDRESS previously to find a specific value from the table.

For your specific needs (column numbers that are not evenly spaced) try these formulas.

I have pasted your table into Cell A1 in a blank spreadsheet. I then used the text to column feature to put the table values into the range B2:K7

You will need to figure out the position of your lookup value in both the width and drop ranges.

Width range: It will lookup the value just below your reference value. Add one to it to get the next higher match

=MATCH(A10,B2:K2,1)+1

Drop Range: Same technique as the previous explanation.

=MATCH(B10,A3:A7,1)+1

To get the value from the table use the index function:

=INDEX(B3:K7,B11,A11)

For your values of width = 2500 and drop = 501 I get values of 6, 3 and finally 504.

You can combine the formulas together:

=INDEX(B3:K7,MATCH(B10,A3:A7,1)+1,MATCH(A10,B2:K2,1)+1)