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




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:




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



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"



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)



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