Hlookup pick the row

[Prabhudharun]

Hi..
I have 5X5 array table. I would like to know the project name, if one who save hours in the corresponding project.

for an example if i am selecting the name Name E from drop down list, the output supposed to be

Project C	5
Project D	5

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Below is the sample table

Project\Name	Name A	Name B	Name C	Name D	Name E
Project A	2		9	5
Project B		2		6
Project C		1	7	4	5
Project D	1		6	1	5
Project E		2		2

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

Pls help me solve this

 

[oldbrewer]

								choose a name =	Name E
Below is the sample table
Project\Name	Name A	Name B	Name C	Name D	Name E									Name E
Project A	2		9	5
Project B		2		6
Project C		1	7	4	5									5
Project D	1		6	1	5									5
Project E		2		2
														Name E
	I made 2 helper tables that show only the details for the chosen name							Project C	5
	then I added a column to the lower helper that pulls down the numbers							Project D	5
	from the upper helper table
	clunky - but it works

<colgroup><col><col span="16"></colgroup><tbody>
</tbody>

 

[Prabhudharun]

Please can you elaborate how did you achieve it. As i dont have deep knowledge on excel...can you pls post me the steps.

 

[oldbrewer]

col A

row 1

col J

choose a name

Name E

col O

Project\Name

Name A

Name B

Name C

Name D

Name E

row 2

Project A

2

9

5

Project B

2

6

col K

col L

Project C

1

7

4

5

row 5

1

Project D

1

6

1

5

2

Project E

2

2

Project C

5

3

Project D

5

4

5

col O is a helper column so this will work no matter how many rows you have

cell K5 (the first potential entry in the output table)

immediately under the cell with col K in it

has the formula

=IF(OFFSET($A$2,O5,MATCH($M$1,$B$2:$G$2,0))=0,"",OFFSET($A$2,O5,MATCH($M$1,$B$2:$G$2,0)))

this runs along row 2 from vol B to col G looking for what is in cell M1 (in this case Name E)

and then drops down by the value in O5 (1)

when this is dragged down in col K it finds another blank, a 5, a 5 and a blank

in col J (immediate left of col K where the blank blank 5 5 blank are)

this formula is in J5

=IF(K5="","",OFFSET($A$2,O5,1))

so where there is a blank it returns a blank

and where it is not blank it drops down from A2 by the number in O5 and skips one column to the right

this is also dragged down so Project D is found by

=IF(K8="","",OFFSET($A$2,O8,1))

<colgroup><col><col><col span="15"></colgroup><tbody>
</tbody>

 

[Prabhudharun]

awesome, works great..Thanks a lot

 

[oldbrewer]

we aim to please......