Index match doesnt work?

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
ART DATA.xlsm
AMANAOAPAQAR
1Refreeze NoS1S2S3S4S5
28c(GII),8c(GII)5BB,4BB5AA,5AB5BB
314BB
Master


ART DATA.xlsm
ABCDEFG
1CY NO.Patient Name S1S2S3S4S5
21CINDY HING8c(GII),8c(GII)5BB,4BB5AA,5AB5BB0
32      
43      
54      
CY NO
Cell Formulas
RangeFormula
B2:B5B2=IFNA(INDEX(Master!$E$2:$E$4974,MATCH('CY NO'!$A2,Master!$AM$2:$AM$4974,0)),"")
C2:C5C2=IFNA(INDEX(Master!$AN$2:$AN$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
D2:D5D2=IFNA(INDEX(Master!$AO$2:$AO$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
E2:E5E2=IFNA(INDEX(Master!$AP$2:$AP$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
F2:F5F2=IFNA(INDEX(Master!$AQ$2:$AQ$4974,MATCH('CY NO'!$A2,Master!$AL$2:$AL$4974,0)),"")
G2G2=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:G5G3=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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
ART DATA.xlsm
ALAMANAOAPAQARASAT
1CY NoRefreeze NoS1S2S3S4S5S6S7
218c(GII),8c(GII)5BB,4BB5AA,5AB5BB
314BB
4
5
Master


I realised I didnt capture the column well
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
560
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

ART DATA.xlsm
ALAMANAOAPAQARASAT
1CY NoRefreeze NoS1S2S3S4S5S6S7
218c(GII),8c(GII)5BB,4BB5AA,5AB5BB
314BB
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 ?

1617181469418.png
 

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
=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

Well-known Member
Joined
Feb 23, 2021
Messages
560
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

Excel 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

Well-known Member
Joined
Feb 23, 2021
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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 ""


Excel Formula:
=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))),"")
 
Solution

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
thank you so much for your brillant solution !
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top