Excel Formula Help - Pulling non-blank cell in row

[bellsam13]

Hi there,

I'm looking for some assistance creating a formula that will essentially help me with an organization chart report, pulling in the C-Suite Executive for each employee. Current the report is setup as follows:

• Column "I" populates the direct manager unique identifier ; Column "J" populates direct manager name
• Column "K" populates the direct managers manager unique identifies ; Column "L" populates direct managers manager name
• So on and so forth through column "T". "Manager 1 - Manager 6"

The formula that I'm needing assistance with will be in Column "U." I'd like this to essentially pull the highest value of the "Manager" columns on the row. I.e. Joe Shmoe reports to David Test. David Test reports to Lisa Frank. Lisa Frank reports to Jessica Stewart. I'm wanting column "U" to pull Jessica Stewart.

Thank you!

 

[Alex Blakenburg]

Would something like this work for you ?
(Option 2 relies on a column identifier for name vs id)

20240117 Lookup Last non blank bellsam13.xlsx
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Mgr Name1	Mgr ID2	Mgr Name2	Mgr ID3	Mgr Name3	Mgr ID4	Mgr Name4	Mgr ID5	Mgr Name5	Mgr ID6	Mgr Name6	LOOKUP Option1	LOOKUP Option2	LOOKUP ID
2	Nm1	456	Nm2	789	Nm3	321	Nm4	654	Nm5			Nm5	Nm5	654
3	Nm1	456	Nm2	789	Nm3	321	Nm4					Nm4	Nm4	321
Sheet1

Cell Formulas
Range	Formula
U2:U3	U2	=XLOOKUP(TRUE,($I2:$T2<>""),$I2:$T2,,,-1)
V2:V3	V2	=XLOOKUP(1,($I2:$T2<>"")*ISNUMBER(SEARCH("Name",$I$1:$T$1)),$I2:$T2,,,-1)
W2:W3	W2	=XLOOKUP(1,($I2:$T2<>"")*ISNUMBER(SEARCH("ID",$I$1:$T$1)),$I2:$T2,,,-1)