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]

Adding: if there's no exact match on TODAY()+x, the lookup needs to round up, so the returned value is always at least x days from today (no less).

 

[Eric W]

You can probably get by without using any lookup function. Try this array formula:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
Delivery Date
30 days from today
45 days from today

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: right"]2/22/17[/TD]
[TD="align: right"]3/1/17[/TD]
[TD="align: right"]3/8/17[/TD]
[TD="align: right"]3/15/17[/TD]
[TD="align: right"]3/22/17[/TD]
[TD="align: right"]3/29/17[/TD]
[TD="align: right"]3/23/17[/TD]
[TD="align: right"]3/30/17[/TD]
[TD="align: right"]4/6/17[/TD]
[TD="align: right"]4/13/17[/TD]
[TD="align: right"]4/20/17[/TD]
[TD="align: right"]4/27/17[/TD]
[TD="align: right"]4/25/17[/TD]
[TD="align: right"]5/2/17[/TD]
[TD="align: right"]5/9/17[/TD]
[TD="align: right"]5/16/17[/TD]
[TD="align: right"]5/23/17[/TD]
[TD="align: right"]5/30/17[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3/30/17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4/13/17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]{=MIN(IF((B6:R6>=TODAY()+30)*(MOD(COLUMN(B6:R6),6)<>1),B6:R6))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=MIN(IF((B6:R6>=TODAY()+45)*(MOD(COLUMN(B6:R6),6)<>1),B6:R6))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[itfmm]

This definitely works - awesome! Thank you.

Would you mind laying out how the calculation is being executed? Just want to make sure I understand the fundamentals of it before implementing it.

Thanks!

 

[Eric W]

Sure. Keep in mind that dates in Excel are just stored as numbers. Also, this is an array formula, meaning it works on an array (range) of values, and then usually before it completes, some function picks out the particular value in the array you want.

=MIN(IF((B6:R6>=TODAY()+30)*(MOD(COLUMN(B6:R6),6)<>1),B6:R6))

The part in red looks at each value in the range B6:R6, and decides if it is greater than or equal to TODAY()+30, then returns an array with either TRUE or FALSE for each value, then you'd get something like this array back:

{FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE, ...}

=MIN(IF((B6:R6>=TODAY()+30)*(MOD(COLUMN(B6:R6),6)<>1),B6:R6))

The part in red now decides if we need to skip a column or not. The COLUMN function returns {2,3,4,5,6,7,8,9,10,11,12,13,14,...}, which is the number of the column. The MOD function divides each of those values by 6, then returns the remainder, so we now get: {2,3,4,5,0,1,2,3,4,5,0,1,2,...}. You said you want to skip every 6th column, and each of those columns have a remainder of 1. So if you compare that array with 1 (<>1), you get: {TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,...}

=MIN(IF((B6:R6>=TODAY()+30)*(MOD(COLUMN(B6:R6),6)<>1),B6:R6))

For array formulas the * (times) acts as an AND, both values in the same spot in both arrays must be TRUE for the whole comparison to be TRUE. Think of TRUE as being 1, and FALSE as being 0, and see what you get when you multiply 0*0, 0*1, 1*0, and 1*1. So combining the above 2 arrays, you'd get something like:

{FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE,...}


=MIN(IF({FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE,...},B6:R6))

The IF takes that array, and if the value in that position is TRUE, it takes the value from B6:R6. Since we did not designate anything for the "if false" part of the IF, it defaults to FALSE, so we get:
{FALSE,FALSE,FALSE,FALSE,FALSE,42824,FALSE,42431,...}

Finally, the MIN takes the least value, ignoring non-numeric values. If you go to the Formulas tab and click on Evaluate Formula, you can see how this all works.

Glad I could help!

 

[itfmm]

Really appreciate your detailed explanation! Here's one more question which may end up sounding exceedingly ignorant:

When I changed the location of my Delivery Dates row, shifting it to a new tab and also 4 columns over, I had to change the divisor (skipping every 6th value) to 10 in order for the lookup to pull in the correct date for the first month. However, that breaks the lookup for anything beyond TODAY()+30 (or as today's date moves further into the future). How do I redress that?

 

[Eric W]

If I understand correctly, you're still skipping every sixth column, just different columns. So to do that, leave the divisor at 6, and change the remainder you're looking for:

MOD(COLUMN(B6:R6),6)<>5

If you moved everything over 4 columns, I'd expect you'd need a 5. Move it over 1 more column, and you'd need a 0.

 

[itfmm]

You are a genius, sir! I can't thank you enough - would make you cookies if I could! haha Again, I'm exceedingly appreciative of your help.

 

[Jonmo1]

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

WHY do you want to skip those dates? (other than because it's every 6th column)
Not trying just to be pushy, but seriousely asking.
Perhaps the reason for skipping them may reveal another method to actually skip them..

Is there another row that has some kind of marker that might indicate which ones should be skipped ?

 

[itfmm]

So this is part of delivery schedule that is received from a client. Every sixth value is "last day for approval" and not an actual delivery date, so the lookup should never land on that date as a delivery option. I want to spend as little time as possible formatting outside resources and simply dump this (and other) clients' schedules in a workbook with a dedicated "dashboard" tab that would pull in all of my dates automatically.

Another client's delivery schedule exists in a column, instead of a row as in the above example. I should be able to amend COLUMN to ROW without issues to accommodate the different format.

 

[Jonmo1]

Ok, so is there a Header row? And is there a value in that header row common among those every sixth value ?

It may be more efficient to try using that value, instead of using column functions to count columns.

Example
=MIN(IF((B6:R6>=TODAY()+30)*(B5:R5<>"last day for approval"),B6:R6))