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