CODEWITHSUNDEEP
excelexcel-formula
mp3duck
mp3duck

Reputation: 2663

Two-way look up (or is it HLookup) formula needed

Grid

I have the above grid in Excel..I would like to find a formula that would allow me to use the name in column A as a look up value, and return the value in Row 1 which is chosen by that person (i.e. a "1" in the column).. Like shown below:

What I want

Any ideas?

Upvotes: 0

Views: 97

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Use this formula, say, in I2 and grag it down:

=INDEX($B$1:$H$1,1,MATCH(1,B2:H2,0))

enter image description here

OR, if you have somewhere unsorted list of names and want to get choosen item, there is another formula:

=INDEX($B$1:$H$1,1,MATCH(1,INDEX($B$2:$B$8,MATCH(J2,$A$2:$A$8,0)):INDEX($H$2:$H$8,MATCH(J2,$A$2:$A$8,0)),0))

where your unsorted list of names started at J2. Write above formula in K2 and drag it down as shown in image below:

enter image description here

Upvotes: 3

CRondao
CRondao

Reputation: 1903

But if you want to get the Item based on name, you can write:

=INDEX($B$1:$H$1;MATCH(1;OFFSET($B$2:$H$7;MATCH(I7;$A$2:$A$7;0)-1;0;1);0))

In this case you get the Item based on name in I7

Upvotes: 0

Related Questions