Complicated Match/Lookup in between dates formula?

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,222
Office Version
2007
Platform
Windows
Try this Array formula on sheet1

{=IFERROR(INDEX('Weekly Run Spots'!$E$2:$E$14,MAX(IF(('Weekly Run Spots'!$O$2:$O$14=E2)*('Weekly Run Spots'!$K$2:$K$14<=C2)*('Weekly Run Spots'!$L$2:$L$14>=C2),ROW('Weekly Run Spots'!$A$2:$A$14)))-1),IFERROR(INDEX('Weekly Run Spots'!$E$2:$E$14,MAX(IF(('Weekly Run Spots'!$O$2:$O$14=E2)*('Weekly Run Spots'!$L$2:$L$14=MAX(IF('Weekly Run Spots'!$L$2:$L$14<C2,'Weekly Run Spots'!$L$2:$L$14))),ROW('Weekly Run Spots'!$A$2:$A$14)))-1),"Number not exists"))}

Notes:
- The dates on sheet1 in column C must contain only the date, they currently have date and time.
- Change 14 in all ranges of the formula for the last row with data from the "Weekly Run Spots" sheet.


Attach the file with the test:

https://www.dropbox.com/s/2vbrgjftqdhu822/Sample Data.xlsx?dl=0
 

Watch MrExcel Video

Forum statistics

Threads
1,089,969
Messages
5,411,566
Members
403,379
Latest member
aarango

This Week's Hot Topics

Top