+ Reply to Thread
Results 1 to 3 of 3

Index Match formula to skip blanks and return next greatest value

  1. #1
    Registered User
    Join Date
    02-03-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    22

    Index Match formula to skip blanks and return next greatest value

    In the attached file, I have a index match formula in cell E1 to return an approximate value from the data set to first match the closest date to the date entered in cell B1 without going over, then matches the closest interval within the row without going over the interval entered in cell B2 and skipping blank cells. I want to change part of the formula to find the nearest interval without going under the interval entered in cell B2 and skipping blanks. In short, I want the formula in cell E1, to return the 5% from cell E10 instead of the 3% from cell C10 with the current inputs I have entered in cells B1 & B2. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,889

    Re: Index Match formula to skip blanks and return next greatest value

    So, I think the below should work for you, and should also account for scenarios where there are no valid entries for closest date

    below will finds closest position in the matrix, populated with a %, that complies with B1/B2 criterion -- date taking precedence
    so, if for ex you removed contents of B10:F10 the formula will return 2.8% as it drops back to 27-Mar records, if you then clear D9 it would return 2.7% etc.


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


    as noted above this does not require Array entry.
    Last edited by XLent; 03-06-2019 at 05:45 AM. Reason: reworded narrative to provide steps to illustrate how it will recalc

  3. #3
    Registered User
    Join Date
    02-03-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    22

    Re: Index Match formula to skip blanks and return next greatest value

    Thank you. Works great!

+ 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] Index Match Small Rows - Trying to return w/out blanks
    By tryingtoexcelatexcel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-22-2017, 05:31 AM
  2. [SOLVED] Using INDEX MATCH formula to return values that will skip blank cells
    By mark_luke in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-12-2017, 06:11 PM
  3. Need help to skip blanks in repeated list formula (index match)
    By iskz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2017, 07:37 PM
  4. [SOLVED] Change Index/Match Formula to Skip if formula returns N/A?
    By Jamidd1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-26-2017, 06:36 PM
  5. [SOLVED] returning greatest and lowest value involving index match formula
    By bridge4444 in forum Excel General
    Replies: 6
    Last Post: 08-10-2016, 07:04 PM

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