1

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
0

It becomes much easier if your output looks like:

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.