Automating a choosing process in Excel

Question

The title says practically nothing, so I'll try to explain.

I have some calculation which is based upon some input parameters, doesn't matter now. Out of it (the calculation) I get a number, let's say 98.

On the other side I have a table, with two columns, with models and values corresponding, and based on that number I have to pick a model with a value which is the closest to the number, but greater than it (first higher).

How could I automate that part? So that the model is the "result" of the calculation?

What I'm looking in general is, how to test value>number and then display model from the side column, somewhere?

Hope this makes some sense ...

Answer 1

you're looking to index and match. =index(array,rownum,colnum) where rownum=match(lookup_value,lookup_array,match_type)

So if you values in A1:A4 and models corresponding in B1:B4 and values are sorted descending and you want to lookup the value in C1 and return the next highest model your formula would be:

=INDEX(A1:B4,MATCH(C1,A1:A4,-1),2) where you index the entire array, match the value to the row number in the values column, -1 match type (greater than), and return column 2 which is models.

Does this make sense and match what you're trying to do? Remember to use match type = -1 your values must be descending

Answer 2

That sounds like Excel's Lookup function. Assume your function is in D4, and your table values are in F6:F8, with the models in G6:G8, Then using the formula:

=LOOKUP(D4,F6:F8,G6:G8)

Would return the model closet to the value, but not greater.

Answer 3

I've had prior success with a standard MATCH (true) to get a fuzzy match, then just add one...

=IF(NOT(ISNUMBER(MATCH(A1, G:G, 0))), INDEX(H:H, MATCH(A1, G:G, 1) + 1), INDEX(H:H, MATCH(A1, G:G, 0)))