I have a working INDEX lookup function using an array, however due to the data size it takes too long to produce results using arrays
{ IFERROR(INDEX(Notes!$B:$B,MIN(IF((Notes!$A:$A=$A2)*(Notes!$B:$B<>""),ROW(Notes!$A:$A)),9^9)),"") }
This will lookup lookup cell $A2 in Notes!$A:$A column, find a match and then display the first non-bank corresponding cell row text from Notes!$B:$B
eg:
if A2 = Henry, the result will be 32, because the prior results are blank (text/formula results = "")
Notes (tab):
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Name[/TD]
[TD="class: xl63, width: 64"]Result[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]-> [/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]-> 1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]-> 3[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD="align: right"]->11[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD="align: right"]->32[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD="align: right"]->84[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD="align: right"]->62[/TD]
[/TR]
</tbody>[/TABLE]
Is there an alternative way to do a similar thing without using arrays? (ie using a reference column to product the first non-BANK cell in a corresponding column - similar to a VLOOKUP or INDEX/MATCH)
{ IFERROR(INDEX(Notes!$B:$B,MIN(IF((Notes!$A:$A=$A2)*(Notes!$B:$B<>""),ROW(Notes!$A:$A)),9^9)),"") }
This will lookup lookup cell $A2 in Notes!$A:$A column, find a match and then display the first non-bank corresponding cell row text from Notes!$B:$B
eg:
if A2 = Henry, the result will be 32, because the prior results are blank (text/formula results = "")
Notes (tab):
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Name[/TD]
[TD="class: xl63, width: 64"]Result[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]-> [/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]-> 1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]-> 3[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD="align: right"]->11[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]->[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD="align: right"]->32[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD="align: right"]->84[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD="align: right"]->62[/TD]
[/TR]
</tbody>[/TABLE]
Is there an alternative way to do a similar thing without using arrays? (ie using a reference column to product the first non-BANK cell in a corresponding column - similar to a VLOOKUP or INDEX/MATCH)