# Automating a choosing process in Excel

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 ...

-

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)))

-

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.

-
The problem with lookup, vlookup and hlookup in this problem is that OP wants the greater value – Raystafarian Feb 28 '12 at 18:58

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

-