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