Results 1 to 6 of 6

Thread: Lookup Formula Not Erroring Out When Value Not Found Looking Up Approximate Value

  1. #1
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    13

    Lookup Formula Not Erroring Out When Value Not Found Looking Up Approximate Value

    I have dates ranges (not all months individually) e.g 01/07/2010 - 30/06/2011, while applying lookup formula I get the last value while looking up for a date within the range even if the date is not within the range. I have attached the sample sheet for easy reference.

    Thanks
    Attached Files Attached Files
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    If you look at the LOOKUP function - Excel - Office.com explained by microsoft, it is clear that If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value

    For your question specifically, your formula could be =INDEX($B$2:$B$9,MATCH(A12,$A$2:$A$9,0))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    13
    Thanks, however, this formula brings only the values if they are present in the range, if not it says N/A#. If I extend the date ranges in lookup formula then it works as it shows in attached sheet.

    Thanks
    Attached Files Attached Files
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Just use the IFERROR function

    =IFERROR(INDEX($B$2:$B$9,MATCH(A12,$A$2:$A$9,0))," Date Not Found")
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    13
    Actually I wanted to work within the ranges of dates, i.e if the date falls within 01/07/2010 - 30/06/2011 if the value in B column is 0.3 and date is 29/06/2011 or 02/07/2010 then it should bring 0.3, which I achieved through extending the dates ranges in lookup formula itself. But if there is any simple way to do that, kindly let me know as some time date ranges are many and individual date would be difficult.

    Hope I have clarified this now.

    Thanks
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    You can do it using =INDEX($B$2:$B$25,MATCH(B28,$A$2:$A$25,1))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. LookUp Value and Concatenate All Found Results
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 48
    Last Post: 10-31-2019, 07:00 AM
  2. Lookup Picture Using Formula Without Using VBA
    By paul_pearson in forum Excel Help
    Replies: 16
    Last Post: 11-06-2013, 01:28 PM
  3. Lookup Picture Using Formula Without Using VBA
    By mahmoud-lee in forum Excel Help
    Replies: 3
    Last Post: 06-13-2013, 06:53 PM
  4. Use Native Excel Function Lookup Formula In VBA
    By ramananhrm in forum Excel Help
    Replies: 8
    Last Post: 05-03-2013, 09:10 AM
  5. Lookup Formula
    By excel_learner in forum Excel Help
    Replies: 1
    Last Post: 02-01-2012, 03:08 PM

Posting Permissions

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