Find value in a table that matches same value in rows

**anonymousny** · 06-10-2013, 03:06 PM

Hi -
I have a table called Leaders that contain names of employees falling under L1 (Leader 1) or L2 (Leader 2). I have a worksheet called Employees and all the people they report to going up to potentially 11 reporting levels. I want to find the L1 & L2 in all 11 reporting levels and have their name displayed in the L1 and L2 columns. Please see the attached Leaders file. Any help would be greatly appreciated. I keep searching for a similar posting but it usually involves an index & match where a specific string is being searched but not a value on a table. Thanks!

**Ace_XL** · 06-10-2013, 04:39 PM

In M2
=INDEX($A2:$L2,SUMPRODUCT(($B2:$L2=Leaders!$A$2:$A$7)*COLUMN($B:$L)))

In N2
=INDEX($A2:$L2,SUMPRODUCT(($B2:$L2=Leaders!$A$10:$A$19)*COLUMN($B:$L)))

Copy down