Forum Discussion

Peta_Stolle77's avatar
Peta_Stolle77
Copper Contributor
Jul 06, 2022

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

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)

 

 

excel
Formulas and Functions

3 Replies

  • Peta_Stolle77's avatar
    Peta_Stolle77
    Copper Contributor
    Jul 06, 2022
    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
    • robertparson's avatar
      robertparson
      Copper Contributor
      Feb 28, 2024

      Peta_Stolle77 Thanks Pete, I have been trying to fix that same error for months and never noticed that the formatting was wrong. post found almost TWO years after you posted it.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 06, 2022

    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))

Resources