Non-Array Index Lookup (first not blank)

[qlander]

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):

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)

 

[AhoyNC]

Not sure if this will be faster, but maybe:

Excel Workbook
	A	B
1
2	Henry	32
3
4
5	Name	Result
6	Andrew
7	Henry
8	Joe	1
9	John	3
10	Henry
11	Andrew
12	John
13	Andrew	11
14	Joe
15	Henry	32
16	Andrew	84
17	Henry	62
Sheet

 

[qlander]

Not sure if this will be faster, but maybe:
....

Spreadsheet Formulas

Cell Formula B2 =INDEX($B$6:$B$17,AGGREGATE(15,6,(ROW($B$6:$B$17)-ROW($B$6)+1)/(($A$6:$A$17=$A$2)*($B$6:$B$17<>"")),1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Note sure, however can you please clarify:
1) $A$2 = fixed? There will be many query rows with different lookups, so $A2 more appropriate?
2) I have a variable row length for the data source import, so rather than $B$6:$B$17 (fixed), changing this to $B:$B?
3) Not sure what -ROW($B$6) refers to....I have the data in a different tab - would this be better reflected as -ROW(NewTab!$B:$B) ? I tried this and -ROW(NewTab!$B$2) but it broke the formula

Thanks

 

[AhoyNC]

Referencing whole columns in a formula is inefficient and can slow calculations down. When using the IFERROR function (as in your first formula). The function doesn't look at just the rows that have data, but runs through all 1 million plus rows in the sheet. If your data starts in row A2 and would not go below say row 15,000, then you would be better off setting your range at A2:A15000. If you copy that formula down the rows each formula is looking at all 1 million plus rows which will really start to slow things down.

You would need to change the ranges in my formula to match your data.
1. Yes you probably need to change $A$2 to $A2 if coping down the row.
2. Would make the range as small as possible to cover your data and not reference a whole column.
3. This part of the formula (ROW($B$6:$B$17)-ROW($B$6)+1) is just returning the row numbers or in the example above {1;2;3;4;5;6;7;8;9;10;11;12}

So, maybe something like the example below.

Excel Workbook
	A	B
1	Name	Result
2	Andrew	11
3	Henry	32
4	Joe	1
5	John	3
Sheet1

Excel Workbook
	A	B
1	Name	Result
2	Andrew
3	Henry
4	Joe	1
5	John	3
6	Henry
7	Andrew
8	John
9	Andrew	11
10	Joe
11	Henry	32
12	Andrew	84
13	Henry	62
Notes