Hierarchy Lookup

[JohnnyGee]

Hi all...there is probably a straightforward answer to this question though for the life of me I can't work it out myself!

I have a code hierarchy structure. For each code i.e. shown in column B, I want to be able to find the lowest level the code goes to within the hierarchy based on a specific parameter (where Level A is the highest level and Level F is the lowest level)
For example, if I want to find the lowest EP category level for code 3610022, the correct answer would be EP5212 in cell J5.
Another example would be for code 3610089, the lowest level code would be EP2500 in cell H13.

To begin with, I guess I could use an Xlookup to pull through all levels for the codes from the Data Table. The difficult part after that is working out how to pull through the lowest level code where in theory it could be any column between Level A and Level F. I also want it to be flexible where I can search for other lowest level codes i.e. EW, EA etc.

hopefully someone can assist this novice!

 

[JamesCanale]

365 seems like a good bet for your version:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Code			Code	Level A	Level B	Level C	Level D	Level E	Level 5
2	3610010	EP5140		3610010	EP1000	EP1700	EP5000	EP5100	EP5140
3	3610022	EP5212		3610022	EP1000	EP1700	EP5000	EP5200	EP5212	MT5212
4	3610024	EP5200		3610024	EP1000	EP1700	EP5000	EP5200
5	3610037	EP5000		3610037	EP1000	EP1700	EP5000
6	3610058	None Found		3610058	EA1000	EA2000	EA3000
Sheet23

Cell Formulas
Range	Formula
B2:B6	B2	=LET(a,INDEX($D$2:$J$6,XMATCH(A2,$D$2:$D$6,0),),b,FILTER(a,LEFT(a,2)="EP"),c,TAKE(b,1,-1),IFERROR(c,"None Found"))

 

[JohnnyGee]

Thanks so much...the initial signs are very positive. I understand a lot of formulae in terms of what they are doing but I tend to struggle a bit with the syntax!

 

[JohnnyGee]

Having had a bit of a play around with the formula is there a way of adapting it, so for Row 3, say I want to return the lowest level EP or MT level? The formula would recognise both are present but would return J3 as the result as it's the lowest level of the two filter parameters that are in place. I've tried the following but it doesn't work.

=LET(a,INDEX($D$2:$J$6,XMATCH(A2,$D$2:$D$6,0)),b,FILTER(a,LEFT(a,2)="EP")+(LEFTa,2)="MT")),c,TAKE(b,1,-1),IFERROR(c,"None Found"))

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You're missing a couple of brackets, try

=LET(a,INDEX($D$2:$J$6,XMATCH(A2,$D$2:$D$6,0),),b,FILTER(a,(LEFT(a,2)="EP")+(LEFT(a,2)="MT")),c,TAKE(b,1,-1),IFERROR(c,"None Found"))

 

[Fluff]

Another slightly shorter version

=LET(a,XLOOKUP(A2,$D$2:$D$6,$E$2:$J$6),TAKE(FILTER(a,(LEFT(a,2)="EP")+(LEFT(a,2)="MT"),"None Found"),,-1))

 

[JohnnyGee]

Another slightly shorter version

=LET(a,XLOOKUP(A2,$D$2:$D$6,$E$2:$J$6),TAKE(FILTER(a,(LEFT(a,2)="EP")+(LEFT(a,2)="MT"),"None Found"),,-1))

Perfect...this works a treat. Thanks also for the heads-up on the account update...it had been a very long time between visits to the message board!

 

[Fluff]

Glad we could help & thanks for the feedback.