Combined formula help

[Flora2021]

Hi, I am not sure if this is possible, but I have data, where a status column is in column G.
I use a Vlookup to return a comment from a tab called "Status codes" This formula is put into column T =VLOOKUP(G2,'Status Codes'!A:B,2,FALSE
The problem I have is that I also have to insert a comment where the dates in column M is more than 3 days older than todays date. I have this formula working in another column W
, =IF((NOW()-M4)>3, "No CLM Update in 3+ days", "") but I need it to also be put into column T but only if the row in blank for column T.Is this even possible?
SO basically only do the Date calculation part, if the "comment" column is blank for that row?

 

[dreid1011]

Are you wanting the second formula to calculate if the Vlookup doesn't find the lookup value (which returns #N/A) or if the return value is blank?

 

[Flora2021]

Are you wanting the second formula to calculate if the Vlookup doesn't find the lookup value (which returns #N/A) or if the return value is blank?

Yes exactly. Thank you

 

[dreid1011]

Yes exactly. Thank you

Do you want the second formula to calculate if BOTH of the below options are true, or just one? And if just one, which one?

The options are:

A) The lookup value in G2 does not exist in the lookup array 'Status Codes'!A:B so VLOOKUP returns the error value "#N/A"

B) The lookup value in G2 exists in the lookup array 'Status Codes'!A:B but there is no associated value in column 2 so VLOOKUP returns 0.

Or give this a try:

=LET(v,VLOOKUP(G2,'Status Codes'!A:B,2,FALSE),IF(v<>0,v,IF((NOW()-M4)>3, "No CLM Update in 3+ days", "")))

 

[Flora2021]

Hi, I tried using the formula you provided (thank you so much) but it is only working to pull in the first part of the Vlookup values. If it does not have a vlookup value and even if it meets the criteria that should prompt it to return the "no clm update " text, its displaying as NA.

 

[dreid1011]

Alright, give this a try:

=LET(n,IF((NOW()-M4)>3, "No CLM Update in 3+ days", ""),v,XLOOKUP(G2,'Status Codes'!A:A,'Status Codes'!B:B,n,0),IF(v<>0,v,n))

 

[Flora2021]

Alright, give this a try:

=LET(n,IF((NOW()-M4)>3, "No CLM Update in 3+ days", ""),v,XLOOKUP(G2,'Status Codes'!A:A,'Status Codes'!B:B,n,0),IF(v<>0,v,n))

That worked. Thank you so much!!

 

[dreid1011]

That worked. Thank you so much!!

You're welcome, and happy to help.

 

[Flora2021]

Okay I am so sorry- but I have to add one more step to this.
I have a date also in column j called OLD ETA date. If this date is more than 2 days in the past from the current date, and it does not already meet one of the criteria above, I need to add the comment ETA DATE CHANGE. This should be the last request for this report. Please and thank you again so much.

 

[dreid1011]

Okay I am so sorry- but I have to add one more step to this.
I have a date also in column j called OLD ETA date. If this date is more than 2 days in the past from the current date, and it does not already meet one of the criteria above, I need to add the comment ETA DATE CHANGE. This should be the last request for this report. Please and thank you again so much.

What cell holds the date in col J?

I think this should work:

=LET(n,IF((NOW()-M4)>3, "No CLM Update in 3+ days", IF((NOW()-J4)>2,"ETA DATE CHANGE","")),v,XLOOKUP(G2,'Status Codes'!A:A,'Status Codes'!B:B,n,0),IF(v<>0,v,n))