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