SOLVED

Using VLOOKUP and TRIM

Brass Contributor

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

johnsboxftm_1-1675449757182.png

 

johnsboxftm_2-1675449778925.png

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:

johnsboxftm_3-1675450069542.png

 

 

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

@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"

january 2023.JPG

 

Sheet "Rates"

rates.JPG

 

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 (Brass Contributor)
Solution

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

trim paste only values.JPGtrim.JPG

 

1 best response

Accepted Solutions
best response confirmed by johnsboxftm (Brass Contributor)
Solution

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

trim paste only values.JPGtrim.JPG

 

View solution in original post