Forum Discussion

johnsboxftm's avatar
johnsboxftm
Brass Contributor
Feb 03, 2023

Using VLOOKUP and TRIM

Someone helped me yesterday with VLOOKUP.  I am not doing it right (of course).  This is what I am trying to do:   I want to type in on sheet "January 2023" cell B3 a specific city name and have ce...
Excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 03, 2023

    johnsboxftm 

    =TRIM(A1)

    You can apply this formula in cell C1 and copy it down. Then you can copy the entire range e.g. C1:C1147 and paste only values. In the example i've pasted only values in column D but you can paste only values in column A as well. Then you can delete all the TRIM formulas from column C.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 03, 2023

johnsboxftm 

=VLOOKUP("*"&B3&"*",Rates!$A$1:$B$1147,2,FALSE)

You can try this formula. It seems that in the "Rates" sheet the city is entered along with the state e.g. " Cleveland, OH" or "Columbus, OH " or " Cincinnati, OH  " whereas in the "January 2023" sheet there is "Cleveland" or "Columbus" or "Cincinnati".

 

Sheet "January 2023"

 

Sheet "Rates"

 

  • johnsboxftm's avatar
    johnsboxftm
    Brass Contributor
    Feb 03, 2023
    I will give it a try. As for the TRIM function ... I want to remove the extra spaces. Do I have to do that for each individual line or can I do the entire column?
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Feb 03, 2023

      johnsboxftm 

      =TRIM(A1)

      You can apply this formula in cell C1 and copy it down. Then you can copy the entire range e.g. C1:C1147 and paste only values. In the example i've pasted only values in column D but you can paste only values in column A as well. Then you can delete all the TRIM formulas from column C.

       

Resources