# Complicated Match/Lookup in between dates formula?

#### DanteAmor

##### Well-known Member

The file does not exists. If you put it back, you must share the file.

### 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
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