Hi!
Lookup Value in D1
Table in the range A1:B10
Array entered:
=IF(ISNA(VLOOKUP(D1,A1:B10,2,0)),INDEX(B1:B10,MATCH(MAX(IF(A1:A10<D1,A1:A10)),A1:A10,0)),VLOOKUP(D1,A1:B10,2,0))
If you have dupes......
Lookup value is 74. 74 does not exsist and there are dupe 73's with
different corresponding values to return, the first match will be returned.
If you want the nth instance............Good luck with that! SORT!
Biff
"John" <[email protected]> wrote in message
news:[email protected]...
>I have a array of numbers that I cannot sort in which I need to find a
>value
> that is the next smallest (or exact) to the value I am looking for. Then I
> want to return a corresponding value in another column, but in the same
> row.
>
> I found if the numbers occurs twice, the LOOKUP commnad will yield the 2nd
> incident of this number. The INDEX MATCH command will work, but only if
> the
> number is an exact match, else same problems as lookup.
Bookmarks