Index Match Problems

Hi All,

I am having problems with getting either the Index Match formula (N7) or the Vlookup/Hlookup (N8) formula in the attached spreadsheet to work as required.

The two problems are:

1. If a distance value less than 100 is entered - the returned value is from the maximum value row, it should be the minimum value
2. If a distance over the maximum value is entered - the returned value is OK exept where it partially matches a row value i.e if 2000 is entered it returns the row value where 200 is present, it should return the maximum value.

The distance actualy falls within a range as shown in columns C and D but I have not been able to find a way to use this in addition to the current formulas.
Is there also a way to round up or down to the next available value if between the min and max value.

Any help is appreciated.

Thanks

I added a named range to each "section" of the table. The names are listed in column J for reference.

Then I adjusted your INDEX/MATCH to use an INDIRECT() reference to your type & definition to select which named range to INDEX.

My formula in N9: =INDEX(INDIRECT(A3&B3),MATCH(C3,$C$8:$C$11, 1), MATCH(D3, $E$7:$H$7, 1))

You can add a key column like so, then use a standard INDEX of the entire table.