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 [TABLE="width: 328"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/07/2018[/TD]
[TD="align: right"]07/07/2018[/TD]
[TD] Due this week[/TD]
[/TR]
[TR]
[TD="align: right"]06/07/2018[/TD]
[TD="align: right"]30/06/2018[/TD]
[TD]1 week late[/TD]
[/TR]
[TR]
[TD="align: right"]29/06/2018[/TD]
[TD="align: right"]23/06/2018[/TD]
[TD]2 weeks late[/TD]
[/TR]
[TR]
[TD="align: right"]22/06/2018[/TD]
[TD="align: right"]16/06/2018[/TD]
[TD]3 weeks late[/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2018[/TD]
[TD="align: right"]09/06/2018[/TD]
[TD]4 weeks late[/TD]
[/TR]
[TR]
[TD="align: right"]08/06/2018[/TD]
[TD="align: right"]02/06/2018[/TD]
[TD]5 weeks late[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]26/05/2018[/TD]
[TD]6 weeks late[/TD]
[/TR]
[TR]
[TD="align: right"]25/05/2018[/TD]
[TD="align: right"]19/05/2018[/TD]
[TD]7 weeks late[/TD]
[/TR]
[TR]
[TD="align: right"]18/05/2018[/TD]
[TD="align: right"]12/05/2018[/TD]
[TD]8 weeks late[/TD]
[/TR]
[TR]
[TD="align: right"]11/05/2018[/TD]
[TD="align: right"]14/04/2018[/TD]
[TD]9-12 wks late[/TD]
[/TR]
[TR]
[TD="align: right"]13/04/2018[/TD]
[TD="align: right"]17/03/2018[/TD]
[TD]13-16 wks late[/TD]
[/TR]
[TR]
[TD="align: right"]16/03/2018[/TD]
[TD="align: right"]17/02/2018[/TD]
[TD]17-20 wks late[/TD]
[/TR]
[TR]
[TD="align: right"]16/02/2018[/TD]
[TD="align: right"]16/02/2017[/TD]
[TD]More than 21 weeks late[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.