Lookup Next Highest Number

[nadavey86]

Hi all, Newbie here.

I have a price list table that I would like to automatically price doors (Height x Width) after entering the width and height in cells M1 and M2.

The table is in cells A1:A10, Heights are in cells A2:A10 and widths are in cells B1:J1. Cell A1 is blank.

The formula I am using works fine if the entered width and height is in the above cells. If the entered value is not there it picks out the next lower number below the value but I would like it to go to the next higher value.

For an example my height column looks like this:

140
175
283
355
445
570
715
895
1245

If I type 720 in cell M1 it will look at 715???

This is the formula i'm using:

=INDEX(A1:J10,MATCH(M1,A1:A10),MATCH(M2,A1:J1))

Any help is much appreciated.

[Jonmo1]

Can you sort the table in DEscending order?
1245
895
715
etc..

?

If so, try
=INDEX(A1:J10,MATCH(M1,A1:A10,-1),MATCH(M2,A1:J1))

[ChemistB]

You'll need to replace each MATCH argument with something a bit more complex.

First, it might be an exact match, then you don't want it to go to the next highest. Right? So we need to check for exact matches. Then we would use your formula (if there is no exact match) +1

So the portion

MATCH(M1,A1:A10) becomes

IFERROR(MATCH(M1, A1:A10,0), MATCH(M1, A1:A10)+1)

Make sense?

[nadavey86]

Thanks for the replys.

That seems to work Chemist B.

How would I put this together for it to look at the height value in M1 and the width value in M2 and return the value in the table?

I have tried figuring this out but with no success.

Thanks in advance.

[ChemistB]

Okay, going back to your original formula

Please Login or Register  to view this content.

First of all, the INDEX should only include the datarange and not the top and side headers so I would think it would be

=INDEX($B$2:$J$10, IFERROR(MATCH(M1, $A$2:$A$10,0), MATCH(M1, $A$2:$A$10)+1), IFERROR(MATCH(M2, $B$1:$J$1,0), MATCH(M2, $B$1:$J$1)+1))

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[nadavey86]

Thanks,

Both formulas worked perfect!