Index Match formula to skip blanks and return next greatest value

**mark_luke** · 03-05-2019, 08:05 PM

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.

**XLent** · 03-06-2019, 05:41 AM

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:

Please Login or Register  to view this content.

as noted above this does not require Array entry.

**mark_luke** · 03-06-2019, 02:39 PM

Thank you. Works great!

Index Match formula to skip blanks and return next greatest value

LinkBack

Thread Tools

Rate This Thread

Display

Index Match formula to skip blanks and return next greatest value

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index Match Small Rows - Trying to return w/out blanks

[SOLVED] Using INDEX MATCH formula to return values that will skip blank cells

Need help to skip blanks in repeated list formula (index match)

[SOLVED] Change Index/Match Formula to Skip if formula returns N/A?

[SOLVED] returning greatest and lowest value involving index match formula

Bookmarks

Bookmarks

Posting Permissions