Search code examples
excelexcel-2013

Excel: Matching Column B with Column A and placing Identifier in Corresponding Column C,D,E,etc

I have a list of names with their direct supervisor that I am trying to expand upon by showing the employees the next few layers down that work for the list of names.

Column C of the linked image brings back results of 1-7 by matching the list of names in A to those in the top row. This shows that "JR" works for "BE" but that "BE" works for "JW" so "JR" is technically under "JW" too. What I am hoping to accomplish is to have a result similar to that shown below.

                Superv.    Employ.
               Column A   Column B   Column C   Column D   Column E

Names to Look for: BE JW AD

                  BE         JR          1         2          3
                  BE         WP          1         2          3
                  JW         BE          2         3
                  AD         JW          3
                  JW         JH          2         3
                  AD         AD          3

These results would then be used to create lists of employees under a certain person.

Names to Look for: BE AD JW

Employees WP JW BE JR BE WP JH JR WP JR

Of course if there is another way to achieve the final result that would be easier then I wouldn't mind an altered format to what I currently have. If anyone has an idea how to achieve this please respond. Thanks

Solution

  • A little change to the output would make this easy:

    In C2 put:

    =A2

    And copy down, this is the immediate supervisor.

    Then in D2 we put:

    =IFERROR(IF(C2=INDEX($A:$A,MATCH(C2,$B:$B,0)),"",INDEX($A:$A,MATCH(C2,$B:$B,0))),"")

    Copy over and down till only empty cells appear:

    enter image description here

    This will return the name and not a number.

    To get a number we can add a simple table that would denote the supervisor number:

    enter image description here

    Then we change the formulas to these:

    In C2:

    =VLOOKUP(A2,I:J,2,FALSE)

    Copy down.

    In D2:

    =IFERROR(IF(C2=VLOOKUP(INDEX($A:$A,MATCH(INDEX($I:$I,MATCH(C2,$J:$J,0)),$B:$B,0)),$I:$J,2,FALSE),"",VLOOKUP(INDEX($A:$A,MATCH(INDEX($I:$I,MATCH(C2,$J:$J,0)),$B:$B,0)),$I:$J,2,FALSE)),"")

    copy down and over.

    enter image description here