why does my xlookup return a space at the front of the results - excel Win 10

New Contributor

I have done Xlookup and the result has come through correctly EXCEPT it has randomly placed a space at the front of the result.

 

So i was expecting  "VT19756-01-50-"  but i got "  VT19756-01-50-"  with a space at the front any clues please as it is effecting my results and data

 

thank you

 

Result with space

 

this was my formula - =XLOOKUP($A75,'Work Order List-20220707'!$A:$A,'Work Order List-20220707'!B:B)

 

 

2 Replies

@Peta_Stolle77 I suspect that the return array ('Work Order List-20220707'!B:B) contains these texts with leading spaces. Clean-up that column or wrap the return array in TRIM.

 

=XLOOKUP($A75,'Work Order List-20220707'!$A:$A,TRIM('Work Order List-20220707'!B:B))

Well how bizarre i just accidentally worked it out - it was the format of the cell - it was on accounting and when i moved it to general all fixed lol

will Leave this up in case it randomly happens to someone else