+ Reply to Thread
Results 1 to 3 of 3

Lookup function help

  1. #1
    Forum Contributor
    Join Date
    01-25-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    146

    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

  2. #2
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    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.
    Click on the star if you think I helped you

  3. #3
    Forum Contributor
    Join Date
    01-25-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    146

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-31-2012, 11:44 AM
  2. Create a lookup formula with an If or Lookup function
    By afountas21 in forum Excel General
    Replies: 2
    Last Post: 09-05-2012, 06:53 PM
  3. [SOLVED] Nested IF Function or LOOKUP Function
    By rstephen in forum Excel General
    Replies: 4
    Last Post: 07-03-2012, 09:42 PM
  4. How to use lookup Function in vba
    By ZarrinPour in forum Excel General
    Replies: 1
    Last Post: 03-12-2006, 05:25 AM
  5. [SOLVED] Pivot table doing a lookup without using the lookup function?
    By NGASGELI in forum Excel General
    Replies: 0
    Last Post: 08-02-2005, 01:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1