UDF to get state names from cell content based on sheet2

[satish78]

UDF need to lookup cell content for city, state names from sheet2 ColumnA
Please see the example link

https://docs.google.com/spreadsheets/d/1Qw0CaBUP1Qo0uaXRO-jlKdT6wJu5xfhrs84QGBzFlLs/edit?usp=sharing

 

[jproffer]

What is supposed to go in A1 and B1?

I tried a city...state, a city...state abr, state....city, state abr.....city.

What result are you wanting?

 

[satish78]

I want UDF to lookup Louisville and (kentucky or KY) in sheet1 columnA from sheet2 columnA and return Sheet2 ColumnB data into Sheet1 ColumnB

 

[jproffer]

Try this in C1 and see what you get.

=IF(ISERROR(FIND(",",A1))=TRUE,VLOOKUP(MID(A1,(FIND(" ",A1)+1),100000),Sheet2!A116:B172,2, FALSE),RIGHT(A1,2))

That's using the third table down on sheet 2 (the one without all the extra commas and quotes and things)

 

[satish78]

Try this in C1 and see what you get.

=IF(ISERROR(FIND(",",A1))=TRUE,VLOOKUP(MID(A1,(FIND(" ",A1)+1),100000),Sheet2!A116:B172,2, FALSE),RIGHT(A1,2))

That's using the third table down on sheet 2 (the one without all the extra commas and quotes and things)

its not working properly. Does not show any results.

 

[jproffer]

That formula only looks at A1. I'm not sure how you had it, the workbook you posted on google only had "Input (something or another)" in A1 while B1 was empty, if I remember right.

I tested "Louisville, KY" and also "Louisville Kentucky" all inclusive in A1, and got "KY" in C1 for both. I didin't see any city names on sheet 2. If that was part of the problem, I apologize. I'll take another look tomorrow at work, as I don't have excel (not real excel at least) at home.

 

[jproffer]

As promised, I checked again this morning at work. I cleared C1 in the workbook I downloaded yesterday and pasted the formula I posted back in, with the same results.

Something worth noting and checking on your input: The formula is currently set up for the format shown below...a comma before an abbreviation but NO comma before a complete state name.

If your data has commas in both cases, the formula can be tweaked for that pretty easily. If you need help with that, let me know.

Excel 2013/2016
	A	B	C
1	Louisville, KY		KY
Sheet1

Cell Formulas
Range	Formula
C1	=IF(ISERROR(FIND(",",A1))=TRUE,VLOOKUP(MID(A1,(FIND(" ",A1)+1),100000),Sheet2!A116:B172,2, FALSE),RIGHT(A1,2))

Excel 2013/2016
	A	B	C
1	Louisville Kentucky		KY
Sheet1

Cell Formulas
Range	Formula
C1	=IF(ISERROR(FIND(",",A1))=TRUE,VLOOKUP(MID(A1,(FIND(" ",A1)+1),100000),Sheet2!A116:B172,2, FALSE),RIGHT(A1,2))

 

[satish78]

its not getting correct data.

Here is detailed info.

In Sheet1 ColumnA Cell with Paragraph with some address anywhere within cell(wrapped).

for example below data from sheet1 A2 from above attachment.
from below data we need to cross check with sheet2 columnA:B range with sheet1 ColumnA range and when matched data found return Sheet2 ColumnB data.
[ Stephani Leadingham 4115 Willowview Blvd, Louisville, KY 40299 cell (407) 221-1104 leadingham@hotmail.comEXECUTIVE PROFILEI have been in the wireless industry since 1997 and my experience has been in Project Management, includingcoordinating, planning and implementing various strategies to complete projects while supervising as many as 50individuals (direct reports and/or sub-contractors). Solid strengths in organization, "do what needs to be done" attitudeand the ability to manage and motivate others. Able to adjust and overcome technical and competitive market challengesas well as having the ability to drive business goals, customer growth, financial profitability and performanceimprovement.EXPERIENCE6/2012 to Present WesTower Communications Louisville, KYSite Acquisition Project ManagerClient: AT&T ]

 

[jproffer]

OHHHH, so the city and state aren't the only thing in the cell? And might even be just anywhere within a sentence? Ok. Then (to be within my knowledge) we're beyond native excel functions now. I'll see if I can work something up tomorrow at work (no excel at home, as I may or may not have said above).

 

[jproffer]

Ok, now I have a new question.

I just re-downloaded your workbook and you have the formula I gave you in it (which is perfectly fine. You're always welcome to use anything someone offers...that goes without saying...that's why we're here), but it's working just as expected for me. UNLESS, of course, as above, you have an entire sentence that happens to contain a City and State name/abbr....then it won't.

So I guess what I'm asking is, is that the case? Does A1 contain more than JUST a city and state name/abbr? Or am I totally off in what I was thinking?