LOOKUP across multiple ranges (noncontinuous references)

itfmm

Board Regular
Joined
Aug 12, 2014
Messages
57
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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I should add that I am staunchly opposed to using VLOOKUP and HLOOKUP, if possible. Thanks!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
While you make a valid point, you could also include MATCH to ID the column/row number for v/hlookup
 
Upvote 0
Here's the sample data:

2i77877.png
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top