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]

Here are both of the available layouts:

This is the second layout - here we are skipping Settlement Date and obviously any blank or text columns:

 

[Jonmo1]

OK, alot to take in there.
I can't make anything out of the 2nd photo, you'd need to clarify which ranges you're looking at and which date you'd expect to return.

But for the first one.

Are those merged cells between rows 11 and 13 ?

=MIN(IF((F14:W14>=TODAY()+30)*(LEFT(F$11:W$11,21)<>"Last Day For Approval"),F14:W14))

 

[itfmm]

Rows 11 and 12 are merged, 13 is independent - so it's either going to contain "Delivery" or "Approval." This actually works better: {=MIN(IF((F14:W14>=TODAY()+30)*(LEFT(F13:W13,8)<>"Approval"),F14:W14))}

As far as the image below, I would need the dates in merged column C:D. That said, here's what I came up with using your function as the backbone: {=MIN(IF((C4:D26>=TODAY()+30)*(E4:F26>-0.08),C4:D26))}

I do actually like this approach a lot better, mostly because I actually understand it fully. Thanks for providing this alternative!

 

[Jonmo1]

You're welcome.

FYI, only the Top/Left cell of a merged range actually contains a value.
So if C4 and D4 are merged, only C4 has a value. D4 is actually blank.

So your formula only needs to reference column C and E
=MIN(IF((C4:C26>=TODAY()+30)*(E4:E26>-0.08),C4:C26))


The formula you had worked coincidentally because you applied the same faulty 2 column array on both arrays...

 

[itfmm]

Right, right - I had just selected the cells, which automatically referenced both of the columns, as opposed to inputting the range manually. Appreciate your help immensely!