Multiple Vlookups based off specific Groups resulting in N/A

[Achdus]

Hi all
This is my first post but the help I've been seeing here is wonderful so I wanted to run this by you:

I am working on a report that contains a main sheet with various names of clients and other vital information. This sheet is then set up to read via a vlookup from a number of additional sheets based off specific information for the clients. So if client Adam is in Group C, I want the vlookup to read from the sheets that pertain to Group C and report back an appropriate date. Based off that info, I calculate the status whether the dates reported back are Overdue or Not due (Overdue=over 365 days, Not due = on sheet for under 30 days or completed within 365 days). However, there are certain clients that appear on my main sheet but do not appear on the sheets for the multiple Groups. Therefore, it is returning an #N/A. I want my Overdue/Not Due status to calculate clients with an N/A as Overdue. I am assuming the easiest way to do this is by adding the #N/A into my status formula but unsure how to do so. Would love some assistance with this.

Formula for Vlookup across the multiple groups:

=IF(E241="Group A",VLOOKUP(D241,'Group A'!A:AI,30,0),IF(E241="Group B",VLOOKUP(D241,'Group B'!A:Q,7,0),IF(E241="Group C",VLOOKUP(D241,'Group C'!A:G,6,0),IF(E241="Group D",VLOOKUP(D241,'Group D'!A:G,6,0)))))

Formula to calculate Overdue/Not Due Status:

=IF(J241>30,IF(L241>365,"Overdue","Not due"),"Not Due")

So is there any way to add #N/A to the status formula to calculate it as Overdue?

Thanks so much!!

 

[Oscar Cronquist]

Try this formula:

=IFERROR(IF(J241>30,IF(L241>365,"Overdue","Not due"),"Not Due"),IF(ISNA(L241),"Overdue",L241))

 

[Achdus]

Try this formula:

=IFERROR(IF(J241>30,IF(L241>365,"Overdue","Not due"),"Not Due"),IF(ISNA(L241),"Overdue",L241))

Rockstar! Thanks