List with between dates + indexmatch/lookup

**calpa** · 08-10-2020, 01:56 PM

Hi,

I have a long list of single dates in column D, and another range of weekly start and end dates along with more data connected to each week.

Is there a way to put a formula next to each single date in column D, and match with the range of weekly start and end dates, to see in which start/end date row/or column, that the single date from column D matches (as to say, when the single date is between a certain weekly dates),

and last but not least, combine that match with either indexmatch or lookup formula to retrieve value thats linked to each weekly range start/end date.??

Of course I can either have the weekly range data put horizontal (rows) or vertical (columns). Maybe helper columns/rows helps in some way!? And i know the formula to check if the single date is between one of the weekly ranges, but I want to check all of them to see which one, to not have to make a super long formula...

Maybe im not explaining clear enough.. Just ask if so

Thanks!

**CARACALLA** · 08-10-2020, 02:06 PM

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

**MrShorty** · 08-10-2020, 02:24 PM

It's not clear exactly what you are trying to do, but it really sounds like you want an approximate match lookup. Unfortunately, almost all lookup examples on the internet are for exact match lookups. Are you familiar with how the approximate match option works? This link is one of the few that shows the behavior of the approximate match option: https://www.ablebits.com/office-addi...ximate-vlookup

My expectation is that, if you build a lookup table with the "start date"/"first day of week" dates (not text) and the accompanying data from each week, you should be able to use =VLOOKUP(date,lookup_table,column_number,TRUE) or HLOOKUP(date,lookup_table,row_number,TRUE) to retrieve the desired information from the lookup table.