IFERROR and XLOOKUP only recognising dates and not text

[SarahSomethingExcel100]

Hi there,
I have a spreadsheet that has columns which show either a date or some text. e.g. '22/04/2024' or 'Ready' (see column AW in the pic below).
When i do an xlookup of that column on another tab, i use IFERROR so that if there are any blanks, then it doesnt show the 0/01/1900 date, but i also want it to take across the text.
=IFERROR(1/(1/XLOOKUP($A2,SiteList!$A:$A,SiteList!AW:AW)),"")
When i do the above formula it only takes across the dates and fails to bring across the text (in the red square in the pic below)

When i do a normal Xlookup: =XLOOKUP($A2,SiteList!$A:$A,SiteList!AW:AW) then this works but it still shows the blanks as 0/01/1900 dates.

Could someone please help me?
Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(x,XLOOKUP($A2,SiteList!$A:$A,SiteList!AW:AW,""),IF(x="","",x))

 

[bobsan42]

when you do 1/1/xlookup it returns text and tries to do math with it, which returns an error then it returns ""
Try if the solution from Fluff works for you.

 

[Alex Blakenburg]

This might be another option:

=XLOOKUP(1,(SiteList!A:A=A2)*(SiteList!AW:AW<>""),SiteList!AW:AW,"")

 

[SarahSomethingExcel100]

Hi & welcome to MrExcel.
How about

=LET(x,XLOOKUP($A2,SiteList!$A:$A,SiteList!AW:AW,""),IF(x="","",x))

Amazing! Thank you so much - this worked!
=LET(x,XLOOKUP($A2,SiteList!$A:$A,SiteList!AW:AW,""),IF(x="","",x))

 

[Fluff]

Glad we could help & thanks for the feedback.