Look for a date between two date ranges and return text (IF AND statment?)

[JOJOFOS]

I hope someone can help, I am trying to write a formula that looks at a date on one workbook(I4), then looks to see if the date falls between two dates on another workbook (Admin col A & B) and returns text showing on another cell if this is true (Admin col C), if false then look at the next row with the same query etc. I have tried using and IF AND formula, but I am obviously doing something wrong, if anyone could tell me how to write the formula for the if statement or tell me a quicker solution I would really appreciate it! :

=IF(AND(I4>=Admin!$A$31,I4<=Admin!$B$31),Admin!$C$31,IF(AND(I4>=Admin!$A$32,I4<=Admin!$B$32),Admin!$C$32,IF(AND(I4>=Admin!$A$33,I4<=Admin!$B$33),Admin!$C$33,IF(AND(I4>=Admin!$A$34,I4<=Admin!$B$34),Admin!$C$34,IF(AND(I4>=Admin!$A$35,I4<=Admin!$B$35),Admin!$C$35,IF(AND(I4>=Admin!$A$36,I4<=Admin!$B$36),Admin!$C$36,IF(AND(I4>=Admin!$A$37,I4<=Admin!$B$37),Admin!$C$37,IF(AND(I4>=Admin!$A$38,I4<=Admin!$B$38),Admin!$C$38,IF(AND(I4>=Admin!$A$39,I4<=Admin!$B$39),Admin!$C$39,IF(AND(I4>=Admin!$A$40,I4<=Admin!$B$40),Admin!$C$40,IF(AND(I4>=Admin!$A$41,I4<=Admin!$B$41),Admin!$C$41,IF(AND(I4>=Admin!$A$42,I4<=Admin!$B$42),Admin!$C$42,IF(AND(I4>=Admin!$A$43,I4<=Admin!$B$43),Admin!$C$43,IF(AND(I4>=Admin!$A$44,I4<=Admin!$B$44),Admin!$C$44,"Check date"))))))))))))))


A B C This commences on row 31

13/07/2018	07/07/2018	Due this week
06/07/2018	30/06/2018	1 week late
29/06/2018	23/06/2018	2 weeks late
22/06/2018	16/06/2018	3 weeks late
15/06/2018	09/06/2018	4 weeks late
08/06/2018	02/06/2018	5 weeks late
01/06/2018	26/05/2018	6 weeks late
25/05/2018	19/05/2018	7 weeks late
18/05/2018	12/05/2018	8 weeks late
11/05/2018	14/04/2018	9-12 wks late
13/04/2018	17/03/2018	13-16 wks late
16/03/2018	17/02/2018	17-20 wks late
16/02/2018	16/02/2017	More than 21 weeks late

<tbody>
</tbody>

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if this does what you need
=INDEX(Admin!$C$31:$C$43,MATCH(I4,Admin!$A$31:$A$43,-1))

Hope this helps

M.

 

[JOJOFOS]

Thank you so much for your quick reply, this works perfectly.

 

[Marcelo Branco]

You are welcome. Glad to help

M.