Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

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

share|improve this answer

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.

share|improve this answer
    
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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.