+ Reply to Thread
Results 1 to 7 of 7

trying to nest LOOKUP within the MIN formula

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    Massachusetts, US
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO Version 2307
    Posts
    15

    trying to nest LOOKUP within the MIN formula

    I want to find the lowest value in a column. I know to use MIN(A1:A100).

    But the A1 column I want to use is partially populated; it has non-zero from A1 to A?, the rest of the cells in the column
    are zero
    So: A1 to A20 are non-zero; A21 to A100 are zero.

    I wanted to use LOOKUP to return the cell in the column with the last non-zero value,
    then use MIN to find the lowest value in the populated cells:
    MIN(A1:last populated cell from LOOKUP)

    But Excel keeps telling me =MIN(A1:LOOKUP(2,1/(A1:A100<>0),ROW(A1:A100))) is not a formula
    "There's a problem with this formula.
    Not trying to type a formula?" etc

    Please tell me what I'm doing wrong.

    I'm a newbie, so I'm here asking for your help.
    Thank you for your help,
    Tom

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: trying to nest LOOKUP within the MIN formula

    You can probably just use the following ARRAY formula (Applied with Ctrl + Shift + Enter):

    =MIN(IF(A1:A100>0,A1:A100))

    - Moo

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: trying to nest LOOKUP within the MIN formula

    Try this:

    =MIN(A1:INDEX(A1:A100,LOOKUP(2,1/(A1:A100<>0),ROW(A1:A100))))

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: trying to nest LOOKUP within the MIN formula

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-02-2017
    Location
    Massachusetts, US
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO Version 2307
    Posts
    15

    Re: trying to nest LOOKUP within the MIN formula

    Thank you for your reply. Yours and the other replies solved my question. Thank you so much.
    Regards,
    Tom

  6. #6
    Registered User
    Join Date
    10-02-2017
    Location
    Massachusetts, US
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO Version 2307
    Posts
    15

    Re: trying to nest LOOKUP within the MIN formula

    Thank you for your reply. The formula you gave solved my question. Thanks so much.
    Regards,
    Tom

  7. #7
    Registered User
    Join Date
    10-02-2017
    Location
    Massachusetts, US
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO Version 2307
    Posts
    15

    Re: trying to nest LOOKUP within the MIN formula

    Thank you for your suggestion, the formula solved my question. Thanks so much, it's greatly appreciated.
    Regards,
    Tom

+ 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. Can you nest Lookup fucntions in an IF function?
    By tb9605 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-12-2021, 11:50 AM
  2. [SOLVED] Trying to Nest Formulas LOOKUP and IF
    By JSelmstar in forum Excel General
    Replies: 5
    Last Post: 04-10-2018, 06:56 PM
  3. [SOLVED] Nest lookup Function to lookup name between date range and return value
    By bbeards in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2018, 01:37 PM
  4. [SOLVED] nest IF formula
    By Debthered in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2014, 03:05 PM
  5. Nest Lookup With If And Statements
    By opeyemi1 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-19-2008, 11:57 AM
  6. Advanced Lookup and Average Nest Function
    By ucdivingcu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-26-2007, 11:10 AM

Tags for this Thread

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