Excel - ROW Function Problem - Expert Solution

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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc