How to Index-Match upwards from a specified cell until non-blank

Question

I've got data in sort of a directory tree layout (See picture)

Basically I want to use one lookup code as a reference point and have excel look upwards in the respective columns to display the "parent directories".

Currently I have an index which tells me exactly what code is what, but I want to setup a lookup tool (See lower table in the image below) that shows me all levels at once. In the picture I inputted the values I want to return using formulas.

So if I input a level 4 code, I want to use formulas that will return the level 4, 3, 2, and 1 programs. For a level 3 code, I want it to lookup to return level 3, 2, and 1 programs.

There are 10,000+ lines of data.

It seems so simple to me.... Start at this row and look upwards in this column until you reach a value..... But it seems it's not so simple.

Any help/tips will be greatly appreciated!

-Alex

Answer 1

As you've noticed, Excel does not natively support "looking upwards." Functions like index() or vlookup() are great for finding the first match, but not the last.

Assuming your data is in cells A1:F10, this formula finds the last non-blank cell in column A, starting from the code (column D) entered in cell H2. It can be autofilled right (to find your other columns) and down (to search for more codes). This is an array formula and must be confirmed with ctrl+shift+enter.

=INDEX(A$2:A$10,MAX(IF(NOT(ISBLANK(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0)))),ROW(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0))),""))-1)

OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0)) is used to dynamically size the lookup array so that it starts at A2, and ends at the row at which the desired code is found. So if your code is located in D5, this piece returns A2:A5

MAX(IF(NOT(ISBLANK(...)),ROW(...),"")) This returns the row number of the highest non-blank cell in the range we previously found.

INDEX(A$2:A$10,...-1) and finally, index() finds the actual value of the cell we found. We subtract one because the lookup range starts at row 2, whereas the row count that we're using, obviously, starts at 1.