+ Reply to Thread
Results 1 to 7 of 7

Index-Match-Lookup for Last Value in a column - Glitch or Solution?

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Hoboken, NJ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Index-Match-Lookup for Last Value in a column - Glitch or Solution?

    Here is the formula I am using: =INDEX(N16:N65,MATCH(LOOKUP(9.99E+307,I16:I65),I16:I65,0))

    My purpose with this is to find the last value entered in a column and return the corresponding value in the same row, different column. For example the last number in Column I was entered for December 3rd, 2013 and is a 4.46 in Cell I33. The cell with the formula above then identifies Cell N33 and reports back the value of that cell, in this case it was -22%.
    The problem I having is if 4.46 exists earlier in column I, the above formula is returning the value from that earlier instance instead of the most recent. Further example, a 4.46 was entered for December 3rd. A 4.46 was previously entered for October 28th, however the formula I have above is reporting row data from that instance instead of what I want - December 3rd.

    Is there a way or formula adjustment to correct this so that I can always have returned the last number entered? I have found a work-around by typing 4.460000001 as a means to distinguish one number from the other without greatly altering my calculations. I am wondering if it is just a glitch as well.

    Thank You
    Brian

  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,841

    Re: Index-Match-Lookup for Last Value in a column - Glitch or Solution?

    Would this do the trick:

    =INDEX(N16:N65,MATCH(9.99E+307,I16:I65))

    - Moo

  3. #3
    Registered User
    Join Date
    04-04-2013
    Location
    Hoboken, NJ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Index-Match-Lookup for Last Value in a column - Glitch or Solution?

    Works Perfectly, thanks Moo! So it would appear originally adding LOOKUP was unnecessary.

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

    Re: Index-Match-Lookup for Last Value in a column - Glitch or Solution?

    Yup, I thought it looked too long for what you were trying to do. Glad to help.

    - Moo

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Index-Match-Lookup for Last Value in a column - Glitch or Solution?

    This formula will also give you the same result

    =LOOKUP(9.99E+307,I16:N65)
    Audere est facere

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

    Re: Index-Match-Lookup for Last Value in a column - Glitch or Solution?

    There ya go! DDL always has a knack for making my formulas look long by comparison!

    - Moo

  7. #7
    Registered User
    Join Date
    04-04-2013
    Location
    Hoboken, NJ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Index-Match-Lookup for Last Value in a column - Glitch or Solution?

    Great! That works too! Thank You as well!

+ 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. Multiple column lookup using index match
    By adaws in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-22-2013, 11:43 PM
  2. Index, Match, and/or SumProduct solution?
    By phrankndonna in forum Excel General
    Replies: 3
    Last Post: 12-11-2012, 07:15 PM
  3. Replies: 0
    Last Post: 11-04-2011, 01:53 AM
  4. INDEX/LOOKUP solution
    By johnmitch38 in forum Excel General
    Replies: 4
    Last Post: 10-26-2011, 11:01 AM
  5. Index/Match Solution?
    By WeatherGuy in forum Excel General
    Replies: 3
    Last Post: 01-31-2006, 06:20 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