Xlookup currency/date format

[kvmike]

Hi,

I am using xlookup to pull some information from another sheet in the same workbook.
The 'return array' is formatted as currency (not sure if this matters), but its not pulling as currency. Its pulling as a general number, but when attempting to format the destination cell to currency it does nothing. I am also having the same issue with another Xlookup not allowing me to format the result as a date.

Any help is much appreciated

 

[Joe4]

Excel formulas only return the values formulas, not the formatting from other cells.
But you should be able to format the results in the resulting cell, unless you formula is coercing the result to text.
Can you post your formula?

 

[kvmike]

Excel formulas only return the values formulas, not the formatting from other cells.
But you should be able to format the results in the resulting cell, unless you formula is coercing the result to text.
Can you post your formula?

Hi, i managed to solve half of my problem.

=XLOOKUP(H50,VMB!B:B,VMB!D:D,"",0)&""

&"" was forcing the formatting issue, which i have now removed.

In terms of the same formula pulling a date in a separate field, if there is no search criteria to go if it is defaulting to giving me a result of 00/01/1900.
How would i go about making sure it just returns a blank result in this case?

 

[Joe4]

Do you mean like:

=IF(H50="","",XLOOKUP(H50,VMB!B:B,VMB!D:D,"",0))

 

[kvmike]

Do you mean like:

=IF(H50="","",XLOOKUP(H50,VMB!B:B,VMB!D:D,"",0))

Perfect thank you!

 

[Joe4]

You are welcome.
Glad I could help!