Formula to find a word in the body of text in a cell...

[dkballs]

Hi, I am trying to create a spreadsheet where it will compare a value in one cell against the value in another. Seems simple and would be if it was an exact match, but its not. So column "A" has the body of an email that has been exported from outlook into excel. Contained within the text will be a patient number. In column "C" will be the patient number that I need to find within the text body of column "A". I then need it to return a value in column "D" that tells me which row has the patient in.

For context there would be around 800 emails in column A and then column C will have about 500 patients. Basically the sheet is intended to see if an email referral has been received against a list of patients that should have been referred. Currently this process is done manually by a clerk! line by line.

So, here is an example of my spreadsheet thus far:

Ideally column D would return a value that tells me which cell the patient data is in. So D2 would say A2 (as there is the exact patient number there. D3 would also return A2 as the patient was referred in on the same email. D4 would return A4 and D5 would return A4.

I have tried a VLookUp with a wildcard but this don't work:

=VLOOKUP(C2&"*",A:A,1,FALSE)

I think I'm stuck! Any help would be gratefully received.

 

[Peter_SSs]

Do you mean something like this?

Book1
	A	B	C	D
1
2	abc 123 def 456 ghi		123	A2
3	xy 987		456	A2
4	qwerty 555 and 666		789	Not Found
5			666	A4
Locate Patient

Cell Formulas
Range	Formula
D2:D5	D2	=IFERROR(ADDRESS(ROW(A$2)-1+MATCH("* "&C2&" *"," "&A$2:A$4&" ",0),COLUMN(A$2:A$4),4),"Not Found")

Question: What do you want if the patient number is found in more than one cell in column A?

Suggestions:
- Investigate XL2BB so we can copy your sample data to test with.
- Update your profile so we know what Excel version(s) you are using.

 

[dkballs]

That's it! (thanks)
I think I have done something wrong though as it doesn't seem to work in my sheet as it clearly does in yours...

Your question is a great one. Is there a way that the formula would display multiple cells?

e.g:

A2,A4 etc... or may a message like "found in multiple emails" if not (this would then prompt the clerk that something is wrong as something has gone very wrong if a patient has 2 referrals!

 

[Yongle]

Late to the party again!
In reply to post#1

Formula in D2 copied down
=IFERROR(ADDRESS(MATCH("*"&C2&"*",A:A,0),1,4),"")

 

[dkballs]

Thanks Yongle. That work beautifully!

I may be asking the impossible, but is there a way that a true result then copy/pastes the content from corresponding cell in column A into column E?

 

[Peter_SSs]

Is there a way that the formula would display multiple cells?

What version of Excel are you using? (Suggest updating your profile so helpers will know what version you are using)

 

[Yongle]

In E2 copied down
=IFERROR(INDEX(A:A,MATCH("*"&C2&"*",A:A,0)),"")

 

[Peter_SSs]

That work beautifully!

Are you sure?
Your patient numbers seem to vary in length, so that formula may well return incorrect results as shown below

Book1
	A	B	C	D
1
2	abc 98712355 def 456 ghi		123	A2
Locate Patient

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ADDRESS(MATCH("*"&C2&"*",A:A,0),1,4),"")

it doesn't seem to work in my sheet as it clearly does in yours..

Give us some data in a form that we can copy to test with. My signature block below has help with that.

 

[Peter_SSs]

I may be asking the impossible, but is there a way that a true result then copy/pastes the content from corresponding cell in column A into column E?

.. and what do you want to do here if the patient number is found in, say, 4 cells in column A?

 

[dkballs]

.. and what do you want to do here if the patient number is found in, say, 4 cells in column A?

If it was in multiple cells in column A it would need to flag an error of some kind, maybe a message in the cell "found in multiple emails".

For context this would need the user to action as there should never be multiple referrals and if there were, the GP would need to be contacted as the patient pathway would need to be clinically reviewed.