Returning a value from a hierarchy table

Hi all! I have spent the better part of a work day trying to figure this out and I feel like I've scoured the internet back and forth using every relevant keyword that I know in an attempt to find a solution.

I have a list of part numbers in the format XXXXXXXXXXXX and a hierarchy key that breaks the number down. The first two numbers refer to the business unit, the next two are the product family and the next three are the specific product (the remaining are irrelevant for my needs).

I was able to get the extensive hierarchy into Excel, but it’s too large and irregular to edit. Here is an example (BU = business unit, PF = product family, SP = specific product):

A	B	C	D	E	F
01					BU 1
	01				PF 1
		001			SP 1
			XXX		NA
				XX	NA
	02				PF 2
		001			SP 1
			XXX		NA
				XX	NA
02					BU 2
	01				PF 1
		001			SP 1
			XXX		NA
				XX	NA
	02				PF 2
		001			SP 1
			XXX		NA
				XX	NA
	03				PF 3
		001			SP 1
			XXX		NA
				XX	NA

On another sheet, I have a list of part numbers in column A, and in columns B, C, and D I would like the BUs, PFs, and SPs, respectively. Getting the BUs in column B is done with a simple vlookup() command, however, it’s the PFs and SPs that are giving me trouble.

Let's focus on the PFs. If I had a part number that was 0202001XXXXX, that should be BU 2, PF 2, SP 1. Since vlookup() will only read the first occurrence, it would give me 0102001XXXXX. I have tried using the indirect() in conjunction with vlookup() so that vlookup() would start from the row where the BU left off, but have not been able to make it work.

In addition, I have tried the following formula with only very limited success:

{=INDEX(F1:F22,MATCH(1,(A1:A22=02)*(B1:B22=02),0))}

This formula would only get me as far as the PF, but does not handle blank cells at all. I had to make the BUs and SPs on the same row for it to work.

Any help would be great, thanks!

Update: I found some sucess with the following formula:

=VLOOKUP(MID(C2,3,2),INDIRECT(ADDRESS(MATCH(LEFT(C2,2),I:I,0),10)):N4606,5,FALSE)

C2 is the cell that holds the part number. The hierarchy table is in columns I through N. The issue with this is that the hierarchy looks like it has to be in the same tab as the list of part numbers. Whenever I try to reference the hierarchy from its own tab, I get an #N/A error. :-/