Table index challenge

[Saturn]

Please see the enclosed file.
I have a table and two reference cells: C22 and C23.
In this case the reference cells read 500/200000 which returns 160, which is the result I want.

The challenge is that the values in the reference cells could be any number, and these number have to be rounded up to the numbers in the tabel.

Could anyone help me with the formula? (INDEX/OFFSET....?)

Saturn

[ben_hensel]

Fed through helper columns. Your data is sorted the wrong way for MATCH to roundup, so we got around that with an IF.

Please Login or Register  to view this content.

[Saturn]

And what is the formula?
There's no problem to sort the the table the 'right' way. Does it help ?

[ben_hensel]

MATCH(input_term, array, 1) finds the greatest value less than or equal to the input term as it walks along the array; so the data has to be sorted in ascending order.

MATCH(input_term, array, -1) finds the smallest value greater than or equal to the input term; it needs the data to be descending.

So you'd have to mirror the table to make MATCH(-1) work... and now that I think about it, I'd want to check to see if that would need logical handling for input_term = cut-off values anyway.

But the MATCH => IF => INDEX across those five cells should do what you want., so it's not like you need to change the table, because the functions work fine the way they are, right?