Question description:
This user has given permission to use the problem statement for this
blog.
I have 2 files. I want to find exact zip code matches, and then find similar hospital names for those that have a zip code match, and then return a number from a different cell in the row.
Solved by M. H. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
28/12/2017 - 05:32
HI, It's pleasure to help you today:)
Excelchat Expert
28/12/2017 - 05:32
Please share me the sample details.
User
28/12/2017 - 05:33
What do you mean sample details?
Excelchat Expert
28/12/2017 - 05:34
Please provide me the sample files with the data to create formulas as per the requirement.
Excelchat Expert
28/12/2017 - 05:34
I hope, vlookup/ match+index functions to get the matching details.
User
28/12/2017 - 05:35
Ok, how do I best provide this to you?
Excelchat Expert
28/12/2017 - 05:36
Please attach in the chat window using paper clip icon.
User
28/12/2017 - 05:37
ok, they are big so I am just going to create some sample files real quick. Give me a minute
Excelchat Expert
28/12/2017 - 05:38
Sure. Please copy the data in the preview.
User
28/12/2017 - 05:42
I want to take the zip in the first file find exact matches and then find facility names that are close....and return the PP#
User
28/12/2017 - 05:42
make sense
Excelchat Expert
28/12/2017 - 05:43
Sure. I can use vlookup for that.
User
28/12/2017 - 05:43
but v look up wont work to find fuzzy matches will it?
User
28/12/2017 - 05:44
for the facility names
Excelchat Expert
28/12/2017 - 05:44
Based on the zip names?
User
28/12/2017 - 05:45
There are 2 criteria....I want zip codes that match and then for the zip codes that match I want facility names that are close...
User
28/12/2017 - 05:45
so in the example using row 3....
User
28/12/2017 - 05:45
the zip is the same so it would then look for the names that are close, but not exact, and return the PIP #
User
28/12/2017 - 05:46
row 4 has the same zip but the names are different so it should not return anything
Excelchat Expert
28/12/2017 - 05:47
Yes. It is giving an error, as it is not matching.
Excelchat Expert
28/12/2017 - 05:47
I can make it blank.
Excelchat Expert
28/12/2017 - 05:47
=iferror(VLOOKUP(D3,I:J,2,false),"")
Excelchat Expert
28/12/2017 - 05:48
Using iferror, if match is not found, then the result can be blank.
User
28/12/2017 - 05:48
is it only finding exact matches?
Excelchat Expert
28/12/2017 - 05:48
yes. That is right.
User
28/12/2017 - 05:48
I don't think you are following me
User
28/12/2017 - 05:49
I want to return fuzzy matches on the facility names if the zips are identical
Excelchat Expert
28/12/2017 - 05:50
What is meant by fuzzy match? Please show me the result in colmn for example?
User
28/12/2017 - 05:51
For example, in row 3. both sheets have the same zip...however one is names "Grace Homes" and the other "Grace Home Facility" I want these to return because the names are similar or "fuzzy"
User
28/12/2017 - 05:52
so it should recognize a match
User
28/12/2017 - 05:52
which yours does in this case...I think
Excelchat Expert
28/12/2017 - 05:52
Ok. I can return facility name.
User
28/12/2017 - 05:53
No, I want the PP# to return...if the facility names are similar and zips are identical
Excelchat Expert
28/12/2017 - 05:53
In that case, i have use index and match.
Excelchat Expert
28/12/2017 - 05:53
ok. I am comparing the zip codes at the moment.
Excelchat Expert
28/12/2017 - 05:54
We have to have one column to be standard to match it.
Excelchat Expert
28/12/2017 - 05:54
Please let me know the column to match and compare the similarities.
User
28/12/2017 - 05:54
hang on , let me change the data set and I think it will make more sense
User
28/12/2017 - 05:56
OK, so I want the PP# from sheet 1 to be returned to cell K11
User
28/12/2017 - 05:57
To do that, I want it to look at zip codes in sheet 1 first
User
28/12/2017 - 05:57
if there is an exact match in the zip code, then look at facility and see if it is similar
User
28/12/2017 - 05:57
if the name is similar return the PP#
User
28/12/2017 - 05:57
make sense?
Excelchat Expert
28/12/2017 - 05:59
Ok. I can check the name from column H to column B.
Excelchat Expert
28/12/2017 - 05:59
column B text is subset of column H. Will it be other way around anytime?
User
28/12/2017 - 06:00
Yes, could be any combination
Excelchat Expert
28/12/2017 - 06:02
That is difficutl. it need to be checked using program only. Formulas can't make the combinations and get the result. But, I am going to try something to check all the combinations.
User
28/12/2017 - 06:02
Excel has an add in called Fuzzy look up...I was assuming that is what we would have used here
User
28/12/2017 - 06:03
if you can only find the ones where colum B is a subset...I will take that at this point
Excelchat Expert
28/12/2017 - 06:03
I am working on it.
User
28/12/2017 - 06:04
thx
Excelchat Expert
28/12/2017 - 06:06
=if(iferror(find(index(B:B, iferror(match(J11,D:D,0),0),1),H11,1),0)+iferror(find(H11,index(B:B, iferror(match(J11,D:D,0),0),1),1),0)>0,index(B:B, iferror(match(J11,D:D,0),0),1),"")
Excelchat Expert
28/12/2017 - 06:06
Here is the formiula.
Excelchat Expert
28/12/2017 - 06:07
I am trying to check both ways and if it finds the match with zip and facility name, it will provide PP#.
Excelchat Expert
28/12/2017 - 06:07
Else, it will be blank.
User
28/12/2017 - 06:08
That formula returns facility name....not what I need which is PP#
Excelchat Expert
28/12/2017 - 06:09
I can get the PP name. I am showing how it is working.
User
28/12/2017 - 06:09
I see..
User
28/12/2017 - 06:09
looks like we are down to 2 minutes
Excelchat Expert
28/12/2017 - 06:10
=if(iferror(find(index(B:B, iferror(match(J11,D:D,0),0),1),H11,1),0)+iferror(find(H11,index(B:B, iferror(match(J11,D:D,0),0),1),1),0)>0,index(E:E, iferror(match(J11,D:D,0),0),1),"")
Excelchat Expert
28/12/2017 - 06:10
I have just changed the column from B:B to E:E to get PP#
User
28/12/2017 - 06:11
ok, thanks...will work using this and see what I can do.
User
28/12/2017 - 06:11
thanks for the help
Excelchat Expert
28/12/2017 - 06:11
Sure. Thank you.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
