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 cell C3 produce the corresponding tax rate from a table I have listed on the sheet named "Rates".

 

I have tried to duplicate the formula that the super nice person helped me with yesterday but I am not doing it right.  Here is what they suggested I do:

 

"Then, in your table you just apply this formula in the Tax Rate column: =VLOOKUP(B2, Sheet5!$A$1:$B$36, 2, FALSE). It will looks like this:

 

 

You just have to take the following precautions:

1. When you add a new city to the list, make sure to expand the range of the table used in VLOOKUP formula;

2. The city name (the look up value) have to be on the left side of the table where you store the tax rates;

3. Keep the last argument as FALSE."

 

I tried these formulas:

=VLOOKUP(B4,Rates!A:B,2,FALSE)

=VLOOKUP(B4,Rates!A1:B1147,2,FALSE)

=VLOOKUP(B4,Rates!$A$1:$B$1147,2,FALSE)

None of them work for me.

 

Secondly, I copied the cities and tax rates from a table online.  For whatever reason, there is a stupid space before the first letter of the city so it's " Cleveland, OH" or "Columbus, OH " or " Cincinnati, OH  ".  I tried using the =TRIM function but it asks for text. 

 

=TRIM(A) makes A1 say "0"

=TRIM(A:A) asks for text I don't understand why

=TRIM(A1:A1147) gives me an error message asking me to undo a previous formula

 

If anyone could please help I have to have this thing turned in TODAY.  I apologize if I sound frustrated.

 

 

  • 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.

     

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

        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