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):
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
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):
Name | Result |
Andrew | -> |
Henry | -> |
Joe | -> 1 |
John | -> 3 |
Henry | -> |
Andrew | -> |
John | -> |
Andrew | ->11 |
Joe | -> |
Henry | ->32 |
Andrew | ->84 |
Henry | ->62 |
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
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)