+ Reply to Thread
Results 1 to 4 of 4

Lookup or Index(match) function not in ascending order and not an exact match

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    3

    Lookup or Index(match) function not in ascending order and not an exact match

    Im trying to create a lookup function that produces the date(column B) of when a certain value is under 10000 (column A). Column A is not in
    ascending order. I have tried lookup functions, as well as index(match) function, but because I am not searching for an exact match, they have not worked. Any advice?

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Lookup or Index(match) function not in ascending order and not an exact match

    Hi -

    It's a little hard to figure out what you are trying to do without an example. Are there multiple values under 10,000? If so, how do you discriminate between them to pick the right one? i would say Index/match will be your best bet along with some if statements, but I'm just guessing.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    06-17-2013
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Lookup or Index(match) function not in ascending order and not an exact match

    yes there are multiple values under 10,000. I actually forgot to include that I am trying to find the first instance that the value dips below 10000

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Lookup or Index(match) function not in ascending order and not an exact match

    OK -

    Attached is a spreadsheet with one way to do it. I use a helper column to find the values less than 10,000. Then I use Index/Match to find the first occurrence.

    Hope this helps.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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