Forum Discussion

Kristen_C's avatar
Kristen_C
Copper Contributor
Jan 31, 2020

Excel is reading data differently with spaces vs without spaces

I have sheet 1 that is reading data from sheet 2  (the data includes spaces), but for some reason when I delete the spaces on Sheet 1 it pulls different data from sheet 2. Here is the formula I'm using: =IF($A3>" ",VLOOKUP($A3,Sheet1!$A$2:$T$18748,9)," ")

 

I thought I needed to use the TRIM function but that didn't work. 

 

Please help! 

excel

4 Replies

  • Charla74's avatar
    Charla74
    Iron Contributor
    Jan 31, 2020
    Try adding ,0 at the end of your lookup. This is the same as FALSE and tells Excel you’re looking for an exact match for A2:
    =IF($A3>" ",VLOOKUP($A3,Sheet1!$A$2:$T$18748,9,0)," ")
      • ChrisMendoza's avatar
        ChrisMendoza
        Iron Contributor
        Jan 31, 2020

        Kristen_C -

         

        I was thinking below, but have not tested. I was really just showing a method to remove " " (spaces) in a string.

        VLOOKUP(SUBSTITUTE($A3, " ", ""),Sheet1!$A2:$T$18748,9)