Looking up the same value in a row and returning multipe corresponding values

Question

I am trying to lookup value 1 in the example below and return corresponding column name. When I use index match, it only looks up the first column which corresponds to 1 in the below example, it looks up Austin to a corresponding value of 1. I also require other matches to locate backups as shown below. example, it looks up Austin.

Workbook

Employee Name    Austin     Janet    Rita    John    Jenson
Skill level        1          2       3       3       1

Desired output

SME    SME 1       SME 1 backup    SME 2     SME 2 backup    SME 3    SME 3 backup 
       Austin      Jenson          Janet     NA              Rita     john

Answer 1

It becomes much easier if your output looks like:

Where the formulas for the first primary, and backup are:

=INDEX(1:1,1,MATCH(B5,2:2,0))
=INDEX($1:$1,1,MATCH(B$5,OFFSET($A$2,0,MATCH(B6,$1:$1,0),1,COUNT($2:$2)),0)+MATCH(B6,$1:$1,0))

respectively, then the backups can be drag filled down, and everything can be drag filled over.

You can wrap the formula with

=IFERROR(<formula>,"")

if you'd prefer blanks over the N/As