Date coming up as 1/0/1900 on blank cell

[ecrodrig]

I have the following formula:

=IF($B2="Lorenzo",(XLOOKUP($E2,[Status.xlsx]Lorenzo!$C:$C,[Status.xlsx]Lorenzo!E:E,"No new date")),IF($B2="Marta",(XLOOKUP($E2,[Status.xlsx]Marta!$C:$C,[Status.xlsx]Marta!E:E,"No new date")),IF($B2="Richard",(XLOOKUP($E2,[Status.xlsx]Richard!$C:$C,[Status.xlsx]Richard!E:E,"No new date")),IF($B2="Katia",(XLOOKUP($E2,[Status.xlsx]Katia!$C:$C,[Status.xlsx]Katia!E:E,"No new date")),IF($B2="Joe Mack",(XLOOKUP(E2,[Status.xlsx]Joe!$C:$C,[Status.xlsx]Joe!E:E,"No new date")), IF($B2="Armand",(XLOOKUP($E2,[Status.xlsx]Armand!$C:$C,[Status.xlsx]Armand!E:E,"No new date")), IF($B2="Ravi",(XLOOKUP($E2,[Status.xlsx]Ravi!$C:$C,[Status.xlsx]Ravi!E:E,"No new date")), "N/A")))))))

Everything works fine except for a couple of rows. Excel is showing those as a 0 rather than a blank cell, so instead of giving me "No new date" as the output, it gives me a 1/0/1900. The format of the column is short date. If I change it to general, it shows a 0. How can I fix this?

Any help would be appreciated. Thanks.

 

[Fluff]

That suggest that the value in E2 was found on the relevant sheet but that col E was empty.

 

[ecrodrig]

That suggest that the value in E2 was found on the relevant sheet but that col E was empty.

That is correct yet if you notice the picture all other rows are coming out as they should and those rows also have column E blank.

 

[Fluff]

Ok, how about

=LET(Sht,SWITCH($B2,"Lorenzo",[Status.xlsx]Lorenzo!$C:$E,"Marta",[Status.xlsx]Marta!$C:$E,"Richard",[Status.xlsx]Richard!$C:$E,"Katia",[Status.xlsx]Katia!$C:$E,"Joe Mack",[Status.xlsx]Joe!C:E,"Armand",[Status.xlsx]Armand!$C:$E,"Ravi",[Status.xlsx]Ravi!$C:$E,"N/A"),IF(ROWS(Sht)=1,"N/A",IFERROR(1/(1/VLOOKUP(E2,Sht,3,0)),"")))

 

[ecrodrig]

Ok, how about

=LET(Sht,SWITCH($B2,"Lorenzo",[Status.xlsx]Lorenzo!$C:$E,"Marta",[Status.xlsx]Marta!$C:$E,"Richard",[Status.xlsx]Richard!$C:$E,"Katia",[Status.xlsx]Katia!$C:$E,"Joe Mack",[Status.xlsx]Joe!C:E,"Armand",[Status.xlsx]Armand!$C:$E,"Ravi",[Status.xlsx]Ravi!$C:$E,"N/A"),IF(ROWS(Sht)=1,"N/A",IFERROR(1/(1/VLOOKUP(E2,Sht,3,0)),"")))

This did not work, now almost everything is coming up as 1/0/1900

 

[Fluff]

That cannot happen with the formula I suggested, it will either return a non-zero number, N/A or a blank ""

 

[ecrodrig]

That cannot happen with the formula I suggested, it will either return a non-zero number, N/A or a blank ""

Ok, something funky was going on with my spreadsheet, sorry about that. Now I get everything blank although I should be seeing dates if a date was present. I did the following:

= Let(x,IF($B2="Lorenzo",(XLOOKUP($E2,[Status.xlsx]Lorenzo!$C:$C,[Status.xlsx]Lorenzo!E:E,"")),IF($B2="Marta",(XLOOKUP($E2,[Status.xlsx]Marta!$C:$C,[Status.xlsx]Marta!E:E,"")),IF($B2="Richard",(XLOOKUP($E2,[Status.xlsx]Richard!$C:$C,[Status.xlsx]Richard!E:E,"")),IF($B2="Katia",(XLOOKUP($E2,[Status.xlsx]Katia!$C:$C,[Status.xlsx]Katia!E:E,"")),IF($B2="Joe",(XLOOKUP(E2,[Status.xlsx]Joe!$C:$C,[Status.xlsx]Joe!E:E,"")), IF($B2="Armand",(XLOOKUP($E2,[Status.xlsx]Armand!$C:$C,[Status.xlsx]Armand!E:E,"No new date")), IF($B2="Ravi",(XLOOKUP($E2,[Status.xlsx]Ravi!$C:$C,[Status.xlsx]Ravi!E:E,"")), IF($B2="Jessie",(XLOOKUP($E2,[Status.xlsx]Jessie!$C:$C,[Status.xlsx]Jessie!E:E,"")), "N/A")))))))), IF(ROWS(x)=0, "Nope",""&x&""))

This works out great except that the dates are coming up as numbers even though my column is formatted as dates. Here is a picture of what I see with my formula above :

 

[Fluff]

The last part of the formula should be just x, you are turning it into text.

 

[ecrodrig]

The last part of the formula should be just x, you are turning it into text.

Hurray, Hurray, it now works!!! Thanks!

 

[Fluff]

Glad to help & thanks for the feedback.