If/Then Excel Formula Needed?

[marriedmanmike]

Big Picture: I have a 700 names and cell numbers I need to sort by state. I have all area codes in a separate tab, column A and their state in B. I'd like Excel to read the column of cell phones, find it's match then add the label to the next column.

Tab 1= List
Tab 2: AreaCode
Names: List, Column F
Cell Phones: List, Column I
Area Codes: AreaCode, Column A
States: AreaCode, Column B

Is this possible?
What formula would I enter in "List, Column I" to have it read and auto enter the corresponding state label?
Thank you for any help!

 

[Fluff]

Can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[marriedmanmike]

Can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hm, problem is it is individual's names and cell phones... Let me see if I can create a sample sheet with made up stuff.

 

[marriedmanmike]

Excel Test File.xlsx
	A	B	C	D	E	F
1	Name	Cell Phone	State (NEED LABEL)	CORRECT ANSWER	Area Code	State or Territory
2	Joe	(207) 234-5678		Maine	207	Maine
3	Bob	(208) 234-5678		Idaho	208	Idaho
4	Joe	(209) 234-5678		California	209	California
5	Bob	(210) 234-5678		Texas	210	Texas
6	Joe	(212) 234-5678		New York	212	New York
7	Bob	(213) 234-5678		California	213	California
8	Joe	(214) 234-5678		Texas	214	Texas
9	Bob	(215) 234-5678		Pennsylvania	215	Pennsylvania
10	Joe	(216) 234-5678		Ohio	216	Ohio
11	Bob	(217) 234-5678		Illinois	217	Illinois
12	Joe	(218) 234-5678		Minnesota	218	Minnesota
13	Bob	(219) 234-5678		Indiana	219	Indiana
14	Joe	(224) 234-5678		Illinois	224	Illinois
15	Bob	(225) 234-5678		Louisiana	225	Louisiana
16	Joe	(228) 234-5678		Mississippi	228	Mississippi
List

Ok, put all the pertinent info in a mini sheet. If formula is correct, Column C will match Column D. So, need to match first 3 digits of cells in Column B with anything from Column E. That better?

 

[Joe4]

Welcome to the Board!

Here is one way, assuming that column B is Text and not formatted numbers.
Put this formula in cell C2 and copy down for all rows:

=VLOOKUP(MID(B2,2,3)+0,E:F,2,0)

If column B is formatted numbers, try this version:

=VLOOKUP(LEFT(B2,3)+0,E:F,2,0)

 

[marriedmanmike]

Ok, after some formatting of the data (removing spaces, dashes and parenthesis as some had them some were just 10 digits), the second formula works. Thanks!

 

[Joe4]

You are welcome.
Glad I was able to help!