Exact Index Match Returns a Value when it Should Not

[Tguillaume]

Hi all,

I am working with healthcare data in two different worksheets. In my first worksheet, I have a patient unique ID (which is simply a concatenation of their full name, sex, and date of birth) as well as their medical spend. Due to privacy reasons some patient full names are masked with asterisks as seen below:


Sheet1
Column A: Column B:

Patient_ID	Spend
JohnSmith1990-01-01	$100
JaneSmith1991-02-02	$150
***********1992-03-03	$200

I want to add the patient's doctor to the table above, which I can do by referencing another worksheet which has the patient ID and the doctor's name, as seen below. Importantly, this worksheet comes from a separate source and does not have any masked patient names.

Sheet2
Column A: Column B:

Patient_ID	Doctor
JohnSmith1990-01-01	Dr. A
JaneSmith1991-02-02	Dr. B
JohnDoe1992-03-03	Dr. C

A key observation here is that there is no way to infer that the patients in the fourth row of each table are the same and therefore they should not match when using an exact index match formula. I used the following formula in Column C of Sheet 1 to get:

=index(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2$A$2:$A$4,0))

Using the EXACT option of the MATCH function I expect the result for **********1992-03-03 to return #N/A. The issue is that this formula is returning Dr. C as the doctor for **********1992-03-03 because I presume it is partial matching on the date of birth.

Does anyone know how to specify that I do not want any partial matches and require #N/A to return if there is not an exact match on each character? I tried using General and Text format to no avail. I thought specifying 0 in the third argument of the MATCH function would prevent this.

Any help is appreciated!

-Tguillaume

 

[jasonb75]

Do you actually have asterisks in Sheet1!A2?

If you do then it is wildcard matching, otherwise there is no reason for a match to be returned.

One thing that you will find is that the results will be incorrect when there is a valid match. The index range and match range need to use the same rows, by using the entire column in index and a smaller range in match, you are creating an offset.

 

[Muhammad_Usman]

Use Substitute Formula to change "*" Sign.

27-08-21 Exp.xlsx
	A	B	C
2	Patient_ID	Spend
3	JohnSmith1990-01-01	$100	Dr. A
4	JaneSmith1991-02-02	$150	Dr. B
5	***********1992-03-03	$200	#N/A
6			#N/A
7			#N/A
8			#N/A
9			#N/A
10
Sheet1

Cell Formulas
Range	Formula
C3:C9	C3	=INDEX(Sheet2!B:B,MATCH(SUBSTITUTE(A3,"*",""),Sheet2!A:A,0))

 

[jasonb75]

Use Substitute Formula to change "*" Sign.

Actually that should be

=INDEX(Sheet2!B:B,MATCH(SUBSTITUTE(A3,"*","~*"),Sheet2!A:A,0))

otherwise it would not match correctly if the name with the "*" symbols existed in sheet2.

 

[Muhammad_Usman]

Actually that should be

=INDEX(Sheet2!B:B,MATCH(SUBSTITUTE(A3,"*","~*"),Sheet2!A:A,0))

otherwise it would not match correctly if the name with the "*" symbols existed in sheet2.

Thanks For Correction.