Non-Array Index Lookup (first not blank)

qlander

New Member
Joined
Dec 26, 2018
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Not sure if this will be faster, but maybe:
Excel Workbook
AB
1
2Henry32
3
4
5NameResult
6Andrew
7Henry
8Joe1
9John3
10Henry
11Andrew
12John
13Andrew11
14Joe
15Henry32
16Andrew84
17Henry62
Sheet
 
Upvote 0
Not sure if this will be faster, but maybe:
....

Spreadsheet Formulas
CellFormula
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
 
Last edited:
Upvote 0
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
AB
1NameResult
2Andrew11
3Henry32
4Joe1
5John3
Sheet1
Excel Workbook
AB
1NameResult
2Andrew
3Henry
4Joe1
5John3
6Henry
7Andrew
8John
9Andrew11
10Joe
11Henry32
12Andrew84
13Henry62
Notes
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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
Back
Top