I have a matrix of numbers and the matrix has column header -- say like this:

a b c d e f 
0 0 0 1 0 0 
0 0 1 0 0 0

What I want to do is to search for each row for the element 1 and return its column header. For example if I scan the first row, I should return 'd'. Similarly if I scan the second row, I should return 'c'. Which combination of formula works good here?

  • =hlookup(bla,blah,blahh,false) is the function you are looking for. I don't have time ATM to do a full answer, but I'll try and be more complete with an offical answer later unless someone else helps before then. – Tyson Jul 27 '14 at 0:18
  • hlookup doesn't seem to work. guess it has got to do with the row index num option. appreciate if you could provide me an example. – kpnane Jul 27 '14 at 2:26
a   b   c   d   e   f       
0   0   0   1   0   0   d   =INDEX($A$3:$F$3,1,MATCH(1,A4:F4,0))
0   0   1   0   0   0   c   =INDEX($A$3:$F$3,1,MATCH(1,A5:F5,0))

Trust this helps

  • @kpnane ;P good luck – Kai Zen Jul 27 '14 at 18:20

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.