LOOKUP across multiple ranges (noncontinuous references)

[itfmm]

Hello all,

Thanks in advance for your help!

This shouldn't be as hard as it's proving to be, but here I am after spending entirely too much time trying to figure this out on my own.

I have to look up a date across a single row while omitting every 6th value in that range to return the closest date to TODAY()+x (with -1 as the match type).

Below is an example of what I've come up with, but the lookup never moves on to the subsequent range to get a more accurate date:

LOOKUP((TODAY()+30),(INDIRECT({"B5:F5","H5:L5","N5:R5"})))

If the function were working correctly, it would be returning "3/30/2017"

Also, tried this but get a #VALUE!

INDEX(B5:F5&H5:L5&N5:R5,MATCH((TODAY()+30),B5:F5&H5:L5&N5:R5,-1))

Thank you again!

 

[itfmm]

I should add that I am staunchly opposed to using VLOOKUP and HLOOKUP, if possible. Thanks!

 

[FDibbins]

I should add that I am staunchly opposed to using VLOOKUP and HLOOKUP, if possible. Thanks!

Can I ask - why?

 

[FDibbins]

INDEX(B5:F5&H5:L5&N5:R5,MATCH((TODAY()+30),B5:F5&H5:L5&N5:R5,-1))

Not quite sure what you are trying to do there, but you have way too many ranges in that.
Syntax should be...
=INDEX(B5:F5,
and if you are using this...
MATCH((TODAY()+30),B5:F5&H5:L5&N5:R5,-1)
that probably means you need to use CTL SHIFT ENTER (array) to enter that formula with.
However, when you combine ranges like that in MATCH, the outcome is range1ANDrange2 combination

Perhaps if you showed some exaples of your data, and what you expect, it would make things easier to help?

 

[itfmm]

Frankly, I've always found those two functions exceedingly limiting, compared to how dynamic INDEX MATCH is. Meaning, with INDEX MATCH I can delete columns and rows as necessary and have all of my lookups shift appropriately on their own.

 

[FDibbins]

While you make a valid point, you could also include MATCH to ID the column/row number for v/hlookup

 

[itfmm]

Here's the sample data:

​

 

[itfmm]

The red dates are the ones that I need to skip.

 

[FDibbins]

sample did not come through

 

[itfmm]

Sorry - should be up now!