Index Match: How to look left and upward until non-blank value is returned from

[Alex50095]

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

Basically I want to use one code as a reference and have excel look upwards in the respective columns to display how the previous levels.

Currently I have an index which tells me exactly what code is what, but I want to setup a lookup tool (See image below) that shows me all levels at once.

So putting for a level 4 code, I want to use formulas that will lookup the level 3, 2, and 1 code. For a level 3 code, I want it to lookup the level 2 and 1 code.

Any help/tips will be greatly appreciated!
-Alex

 

[shg]

It would be easier if you just duplicated the Level 1 to 3 data down the columns, like a database record. That's how Excel thinks.

 

[Thomas2016]

Is this data pulled in from an XML file? The structure looks similar to others I've seen.

 

[Alex50095]

It's originally in a CSV. I did quite a bit of manipulating to get it in that form. There are 10,000+ lines of data so I was hoping there would be some way to index match (or other way) of returning the value I need. 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.

I looked into OFFSET formulas but the number of "upward steps" I have to take to return the parent value is going to be different for each code.

 

[Thomas2016]

OK 1 more question: will the output table 'Code' column values be static? Meaning you're just matching the remaining data in the other columns to a unique index of known 'Code' values?

 

[Alex50095]

In the lookup tool, the CODE field is just what the Index-Match will use as the lookup code. It will lookup the value I type in there and check it against the SFS Code column.

 

[Alex50095]

It would be easier if you just duplicated the Level 1 to 3 data down the columns, like a database record. That's how Excel thinks.

Could you expound on this? Do you mean have the Level 3 directly to the left of the applicable level 4 code?

If possible I'd like to keep the structure of the "Tree" intact. I know If I did that I could just use a regular index match to retrieve what I want, but I was hoping for a cleverer solution (looking up from a reference point until reaching a value). Also I'd just like to see if this is possible as a learning experience.

To clarify about the "tool". The bottom table is one that I filled in the values myself, but would like to have automatically fill upon entering the code.

Thanks for the responses,
-Alex