# Lookup function help

1. ## Lookup function help

Hello,
I am trying to lookup the first >0 value in a range (U17 to u 1500)

the lookup function I have been trying
=LOOKUP(2,1/(U17:U1500>0),H17:H1500) returns the last value in the range. I need the first value in the range instead. How can I do that?

basically if cell U20 has a value >0
and if cell U300 has also a value >0
with the above formula I get the result of H300.
I would need the value of H20 (being the first) as a result not the one of H300 which is further down in the column.

Thank you so much
Nino  Register To Reply

2. ## Re: Lookup function help

you need to sort the values in Descending order and then use this formula:
=MATCH(0,U17:U1500,-1)

Match type = -1: MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.  Register To Reply

3. ## Re: Lookup function help

Found the solution from an other Post. It Works

"Problem:

Retrieving the value of the first non-blank cell in Range1 (cells A2:A7).

Solution:
Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX(A2:A7,MATCH(TRUE,A2:A7<>”",0))}"

Thank you  Register To Reply