MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to match a value >= target value??


Posted by Rajiv Bhushan on June 28, 2001 1:19 PM

I have the following problem,

I have a target value say 0.77, I need to find the 1st row in a lookup
array (that has to remain unsorted) where the value exceeds the target
value.


1. 0.75
2. 0.745
3. 0.755
4. 0.735
5. 0.78
6. 0.76
7. 0.79

The value returned in this example should be either 5 (the row number),
or 0.78 (the value). I cannot use MATCH(), because it requires the column
to be sorted to produce the right answer. How can I do this???

Rajiv


Posted by cpod on June 28, 2001 1:37 PM

If your list is in column A and the number you are matching is in F1 then:

=MIN(IF((A1:A20>F1)*ROW(A1:A20)=0,"",(A1:A20>F1)*ROW(A1:A20)))

This is an array formula and must be entered using Control+Shift+Enter

Posted by Aladin Akyurek on June 28, 2001 1:40 PM

Rajiv

To get the value larger than the target value, use

=INDEX(A1:A7,MATCH(TRUE,INDEX(A1:A7>0.77,0),0))

If you want the address of that value, use

=MATCH(TRUE,INDEX(A1:A7>0.77,0),0)

which is a part of the first formula.

Aladin

=================

Posted by IML on June 28, 2001 1:43 PM

Row Number

You can get the row number by the following array formula:

=MIN(IF((A1:A7>0.77)*(ROW(A1:A7)),(A1:A7>0.77)*(ROW(A1:A7))))

Be sure to hit enter only when control and shift are depressed to make this work properly.

You can also use this in the index formula - something along the lines of
=INDEX(A1:A7,MIN(IF((A1:A7>0.77)*(ROW(A1:A7)),(A1:A7>0.77)*(ROW(A1:A7)))),1)
still hitting control shift enter.

good luck

Posted by IML on June 28, 2001 1:49 PM

Mine is last ... and worst

Sorry about being slow. When there is no data that matches, Cpods returns a zero, Aladin's returns a N/a - both which are useful. Mine seem to mistakenly fetch the first value.

Good luck

Posted by Rajiv Bhushan on June 28, 2001 1:51 PM

Yes! That works. Thanks a lot

Rajiv


Posted by Rajiv Bhushan on June 28, 2001 1:57 PM

Thanks,
That works quite well, and I should have thought of it.
The array solution was less obvious (to me at
least), but still worked quite well

Thanks to everybody

Rajiv Rajiv To get the value larger than the target value, use =INDEX(A1:A7,MATCH(TRUE,INDEX(A1:A7>0.77,0),0)) If you want the address of that value, use =MATCH(TRUE,INDEX(A1:A7>0.77,0),0) which is a part of the first formula. Aladin =================