+ Reply to Thread
Results 1 to 4 of 4

Array Formula Lookup - Bring back Lowest Date + Bring Back Cell Location

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    39

    Array Formula Lookup - Bring back Lowest Date + Bring Back Cell Location

    Greetings

    =MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx

    Following on from a previous post need to add to my (forums) array.

    I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.

    Help is always appreciated.
    Thank you

    Matt

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,579

    Re: Array Formula Lookup - Bring back Lowest Date + Bring Back Cell Location

    =ADDRESS(MATCH(C4,$A$3:$A$32,0)+ROWS($A$1:$A$2),1)
    Does that work for you?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    04-03-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Array Formula Lookup - Bring back Lowest Date + Bring Back Cell Location

    It doesn't seem to like looking at the array's earliest date that it brings back.

    I wonder if it is because there are more than 1 potential cell locations with the same date. Which in that case would mean that I would only need the first location it can find, lowest row location.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Array Formula Lookup - Bring back Lowest Date + Bring Back Cell Location

    The formula provided by ChemistB returns cell address $A$7 which is corresponds to the date in C4. If you want to incorporate earliest date into his formula you can do it too

    =ADDRESS(MATCH(MIN(IF(A3:A32>0,A3:A32)),$A$3:$A$32,0)+ROWS($A$1:$A$2),1)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. [SOLVED] Bring back date from last populated cell in range
    By tiger01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2013, 06:26 AM
  2. Bring back rents per location from data table
    By mr_vic in forum Excel General
    Replies: 7
    Last Post: 10-15-2013, 02:13 PM
  3. [SOLVED] Stop Vlookup bringing back 0's or #REF! and bring back blanks instead
    By Carling73 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-30-2013, 04:43 PM
  4. Replies: 2
    Last Post: 05-15-2012, 10:46 PM
  5. Bring back value based on date
    By mrggutz in forum Excel General
    Replies: 13
    Last Post: 11-02-2010, 09:14 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