Thread: Two dimentional Search for non exact results

So my problem is you can have any board size e.g 3500mm x 620mm ignore the depth. The price matrix has set sizes and is charged at equal to or greater than a given size according to the price matrix. So for example a board 1530mm x 690mm will be charged at £3.25 as it is greater than 1220 lengthwise x 610 width wise on the matrix. Here is the matrix (Lenght runs along the top and width down the side):

	915	1220	1830	2135
305	£1.00	£2.00	£3.00	£4.00
610	£2.25	£3.25	£4.25	£5.25
1525	£3.50	£4.50	£5.50	£6.50

I know how to do a two dimentional search of a matrix and I have got that working. What I'm trying to figure out is a simpler way to calculate which is the correct column and row to search.

This is a simplified version of the table the real one is much bigger. So I have a current way of working through the different sizes by having a greater than or equal to formula for each size on the matrix and then a lookup searches for the first true case and a formula figures out the correct price. It works great but if I want to have multiple queries on the same page I'll need a new set of formulaes for each. What I need in the end is a page such as e.g

2000 x 900 = £4.25
950 x 350 = £1.00
.........
.........

Where the price is automatically calculated from the matrix.

Going on for at least 40 different queries. So I'm wondering if anyone can figure out a way of doing it with out nested if loops or not the way I currently have it as the actual matrix is about 10 x 15 so that is not really practical.

Thanks for any help you can provide.

Hi,

maybe in B1 and A1 the two sizes

=INDEX($C$4:$F$6,MATCH(B1,$B$4:$B$6,1),MATCH(A1,$C$3:$F$3,1))

Regards


Edit: better with absolute references ($$)

Ciao, thanks for the kind feedback.

Anyway, doublecheck and test the formula outputs, if they are all corret.

Greetings

Ok sorry to be a pain but I completed the problem only to find out the range is below rather than above. So for example a peice 1900 x 900 would be £6.50. That was easy to fix by adding one to the matched cases the problem I have now is for a board e.g 900 x 300 it should return £1 but it just returns N/A. Obviously I could manipulate the table but it's fixed and I can't change the table layout. Any idea's? (Also is doesn't matter that it prints #Ref for larger boards as they don't need to be catered for.)

prova.xls

Hi, no problem:

again (same formula) with a "zero" row/column

Please see the file attached to check if it's ok

Regards.

Hi,

try this (old file; no zero column/row):

=INDEX(C4:F6,MATCH(MAX(305,B1),B4:B6,1);MATCH(MAX(915,A1),C3:F3,1))

You have to tweak the formula to your database.

Regards

Perhaps...

=INDEX(C4:F6,MIN(COUNTIF(B4:B6,"<="&A1)+1,3),MIN(COUNTIF(C3:F3,"<="&B1)+1,4))

For bigger matrixes just change the numbers 3 and 4 or

=INDEX(C4:F6,MIN(COUNTIF(B4:B6,"<="&A1)+1,Count(B4:B6)),MIN(COUNTIF(C3:F3,"<="&B1)+1,Count(C3:F3)))]

That should work for any matrix size.