Forum Discussion
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.
=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.
- OliverScheurichGold Contributor
=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"
- johnsboxftmBrass ContributorI 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?
- OliverScheurichGold Contributor
=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.