Formula not working to convert state names to abbreviations

I have been at this all night and no matter what formula I type in I get "The Formula Contains Errors"

In Column 'E' I have a list of states spelled out, I would like to change that to the state abbreviations.

In column 'H' I have the states and in column 'I' the abbreviations.

I am trying to put my formula into column 'F'

I have tried several different formulas found on-line in various forums and I have also tried using a separate sheet.

No matter what formula I use I get the same error in both Excel 2013 and Excel 2003.

Can someone please talk me through this step by step as if I had no clue what I am doing, because after 3 hours of messing around with this, I apparently do not have a clue!

Every formula that uses vlookup has failed

Here is my file without the confidential info.

I will =index formula in a moment.

There is currently no formula in file because I could not get any of them to work.

=index($I$1:$I52,match(E1,$H$1:$H$52,0)) did not work. I can not attach the sample as when I try to save I get the bad formula error.

I'm not sure what I am doing wrong, but I did use your formula as follows in F2:

=index($I$1:$I52,match(E2,$H$1:$H$52,0))

I also just tried pasting the new formula into F2 and the error pops up as soon as I enter.

Maybe a screenshot will help?

Originally Posted by Hawkeye16

Did you take a look at the file I attached? It has both ways of doing it in there.

In Holland we need to use ; instead of , due to currency conventions, otherwise I can't readily see an error in your formula (aside from starting at H2 instead of H1 but that would not cause an error).

Try looking at the file I attached and see what the formulas say.

No I did not see you attached it!!!!

I just opened it, pasted your code over mine and worked!!!!!

I wonder why every other code I tried threw that error.

Thank you SO much you are truly a life saver.