SOLVED

# Using VLOOKUP and TRIM

Occasional Contributor

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

3 Replies

# Re: Using VLOOKUP and TRIM

``=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"

# Re: Using VLOOKUP and TRIM

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?
best response confirmed by johnsboxftm (Occasional Contributor)
Solution

# Re: Using VLOOKUP and TRIM

``=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.