Index match doesnt work?

[mychi11]

ART DATA.xlsm
	AM	AN	AO	AP	AQ	AR
1	Refreeze No	S1	S2	S3	S4	S5
2		8c(GII),8c(GII)	5BB,4BB	5AA,5AB	5BB
3	1					4BB
Master

ART DATA.xlsm
	A	B	C	D	E	F	G
1	CY NO.	Patient Name	S1	S2	S3	S4	S5
2	1	CINDY HING	8c(GII),8c(GII)	5BB,4BB	5AA,5AB	5BB	0
3	2
4	3
5	4
CY NO

Cell Formulas
Range	Formula
B2:B5	B2	=IFNA(INDEX(Master!$E$2:$E$4974,MATCH('CY NO'!$A2,Master!$AM$2:$AM$4974,0)),"")
C2:C5	C2	=IFNA(INDEX(Master!$AN$2:$AN$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
D2:D5	D2	=IFNA(INDEX(Master!$AO$2:$AO$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
E2:E5	E2	=IFNA(INDEX(Master!$AP$2:$AP$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
F2:F5	F2	=IFNA(INDEX(Master!$AQ$2:$AQ$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
G2	G2	=IFERROR(INDEX(Master!$AR$2:$AR$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),IFERROR(INDEX(Master!$AR$2:$AR$4974,MATCH('CY NO'!$A2,Master!$AM$2:$AM$4974,0)),""))
G3:G5	G3	=IFNA(INDEX(Master!$AR$2:$AR$4974,MATCH('CY NO'!$A3,Master!$AL$2:$AL$4974,0)),"")

For S5 (IE cell G2), I would like to get 4BB , but it returns to zero instead can anyone help, please

 

[Alex Blakenburg]

On each of the 2 sheets can you type, =isnumber(a3) and see if they both return TRUE.
On 1 sheet the number is left aligned which tends to be indicative that it might be Text, which would give you a mismatch in data type.
(It could also just be formatting but we need to prove that)

 

[mychi11]

Yes. Both return to be true.
I tried to move the all the text so its all left aligned, but the index match is still not giving the result I am expecting.

 

[mychi11]

ART DATA.xlsm
	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	CY No	Refreeze No	S1	S2	S3	S4	S5	S6	S7
2	1		8c(GII),8c(GII)	5BB,4BB	5AA,5AB	5BB
3		1					4BB
4
5
Master

I realised I didnt capture the column well

 

[Alex Blakenburg]

ART DATA.xlsm
	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	CY No	Refreeze No	S1	S2	S3	S4	S5	S6	S7
2	1		8c(GII),8c(GII)	5BB,4BB	5AA,5AB	5BB
3		1					4BB
4
5
Master

I realised I didnt capture the column well

When you sent the 2nd version, does your comment mean you realised you are returning 0 because you don't have a value in AR2 ?

 

[mychi11]

=IFERROR(INDEX(Master!$AR$2:$AR$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),IFERROR(INDEX(Master!$AR$2:$AR$4974,MATCH('CY NO'!$A2,Master!$AM$2:$AM$4974,0)),"")); in this case i should be able to return to 4BB when AR2 is blank right?

 

[Alex Blakenburg]

No because the formula is relying on that it can't find the CYNo and returns an error.
It can find the CYNo and is returning the blank cell AR2 which shows up as a 0 when returned by a formula.

If you want it to look in the Refreeze column when AR is blank for that row, change your formula to the below:-
Note:

• I have replaced the IFERROR for a check for = 0
• I have also removed sheet references to CY NO, since its the sheet the formula resides on the reference is unnecessary and just clutters up the formula.

=IF(INDEX(Master!$AR$2:$AR$4974,MATCH($A2,Master!$AL$2:$AL$4974,0))=0,IFERROR(INDEX(Master!$AR$2:$AR$4974,MATCH($A2,Master!$AM$2:$AM$4974,0)),""))

 

[Alex Blakenburg]

Actually this one will cover off the IFERROR for both and rearrange the order in a more logical order (and in your original order of evaluation).
If it both return values are blank it will return 0
If either error out it will return ""

=IFERROR(
IF(INDEX(Master!$AR$2:$AR$4974,MATCH($A2,Master!$AL$2:$AL$4974,0))<>0,
INDEX(Master!$AR$2:$AR$4974,MATCH($A2,Master!$AL$2:$AL$4974,0)),
INDEX(Master!$AR$2:$AR$4974,MATCH($A2,Master!$AM$2:$AM$4974,0))),"")

 

[mychi11]

thank you so much for your brillant solution !

 

[Alex Blakenburg]

Thank you. Happy to have been able to help.