I am trying to setup an IF statement so Excel can auto fill state abbreviations that match with the Area Code. I am having some success, until I reach a certian point. I have tried variations of the "&" code. But this only works to a certian point. The code I have that does work (up to 67) is as follows :

=IF(C3=Legend!$C$3,"NJ",”&”IF(C3=Legend!$C$4,"DC", ”&”IF(C3=Legend!$C$5,"CT",”&”IF(C3=Legend!$C$6,"AL ",”&”IF(C3=Legend!$C$7,"WA",”&”IF(C3=Legend!$C$8," ME",”&”IF(C3=Legend!$C$9,"ID",”&”IF(C3=Legend!$C$1 0,"CA",”&”IF(C3=Legend!$C$11,"TX",”&”IF(C3=Legend! $C$12,"NY",”&”IF(C3=Legend!$C$13,"CA",”&”IF(C3=Leg end!$C$14,"TX",”&”IF(C3=Legend!$C$15,"PA",”&”IF(C3 =Legend!$C$16,"OH",”&”IF(C3=Legend!$C$17,"IL",”&”I F(C3=Legend!$C$18,"MN",”&”IF(C3=Legend!$C$19,"IN", ”&”IF(C3=Legend!$C$20,"IL",”&”IF(C3=Legend!$C$21," LA",”&”IF(C3=Legend!$C$22,"MS",”&”IF(C3=Legend!$C$ 23,"GA",”&”IF(C3=Legend!$C$24,"MI",”&”IF(C3=Legend !$C$25,"OH",”&”IF(C3=Legend!$C$26,"FL",”&”IF(C3=Le gend!$C$27,"MD",”&”IF(C3=Legend!$C$28,"MI",”&”IF(C 3=Legend!$C$29,"AL",”&”IF(C3=Legend!$C$30,"NC",”&” IF(C3=Legend!$C$31,"WA",”&”IF(C3=Legend!$C$32,"TX" ,”&”IF(C3=Legend!$C$33,"AL",”&”IF(C3=Legend!$C$34, "IN",”&”IF(C3=Legend!$C$35,"WI",”&”IF(C3=Legend!$C $36,"PA",”&”IF(C3=Legend!$C$37,"MI",”&”IF(C3=Legen d!$C$37,"MI",”&”IF(C3=Legend!$C$38,"KY",”&”IF(C3=L egend!$C$39,"VA",”&”IF(C3=Legend!$C$39,"TX", ”&”IF(C3=Legend!$H$3,"MD", ”&”IF(C3=Legend!$H$4,"DE", ”&”IF(C3=Legend!$H$5,"CO", ”&”IF(C3=Legend!$H$6,"WV", ”&”IF(C3=Legend!$H$7,"FL", ”&”IF(C3=Legend!$H$8,"WY", ”&”IF(C3=Legend!$H$9,"NE",”&” IF(C3=Legend!$H$10,"IL", ”&”IF(C3=Legend!$H$11,"CA", ”&”IF(C3=Legend!$H$12,"IL", ”&”IF(C3=Legend!$H$13,"MI", ”&”IF(C3=Legend!$H$14,"MO", ”&”IF(C3=Legend!$H$15,"NY", ”&”IF(C3=Legend!$H$16,"KS", ”&”IF(C3=Legend!$H$17,"IN", ”&”IF(C3=Legend!$H$18,"LA", ”&”IF(C3=Legend!$H$19,"IA", ”&”IF(C3=Legend!$H$20,"MN", ”&”IF(C3=Legend!$H$21,"FL", ”&”IF(C3=Legend!$H$22,"CA", ”&”IF(C3=Legend!$H$23,"TX", ”&”IF(C3=Legend!$H$24,"OH", ”&”IF(C3=Legend!$H$25,"IL", ”&”IF(C3=Legend!$H$26,"AL", ”&”IF(C3=Legend!$H$27,"NC", ”&”IF(C3=Legend!$H$28,"LA", ”&”IF(C3=Legend!$H$29,"MA", ”&”IF(C3=Legend!$H$30,"NY", ”&”IF(C3=Legend!$H$31,"MA", ”&”IF(C3=Legend!$H$32,"FL", ”&”IF(C3=Legend!$H$33,"WA", ”&”IF(C3=Legend!$H$34,"TX", ”&”IF(C3=Legend!$H$35,"UT", ”&”IF(C3=Legend!$H$36,"FL","N/A")))))))))))))))))))))))))))))))))))))))))))))))) )))))))))))))))))))))))


I have a legend of all area code setup on the second sheet. I need to be alble to do this with ALL area codes. Any help would be appreciated! Thanks!

This would be far better suited to a VLOOKUP.

If you need help with that, let me know.

Spencer.

I would appreciate the help yes. I have started to attempt that function, but I haven’t had a lot of experience using the VLOOKUP. Thanks!!!

Have a look at the attached for a cut down version of what you're trying to do.

Let me know if it doesn't make sense or you need more help.

S.

That's very neat Spencer. On quick question though, this phone number is in a pre-populated list of information already. Has customer name, address, phone etc. I need to be able to drop code in the (STATE ABBV) cell that auto fills the state abbreviation all the way down after pasting the info into excel. So using the drop down might not work. Example :

John S. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
John D. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
John A. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
John V. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
*REPEAT 1,000's of Times*

The "|" character represents a new cell in Excel.
Please see the attached example. Thanks!!!!!!

Could you provide an example workbook? Either here or to the email address I provided.

You can use dummy data to anonymise it but the setup of your workbook will make it quicker to give you a working solution.