Using MATCH outputs in another cell containing an INDEX-SMALL array

[bryce2563]

Hi I hope someone can help me...
I have a 2 part query regarding MATCH INDEX and SMALL formulas.

I have been trying to create a personnel database that returns peoples names that have experience in specific nominated sectors.

Query Part 1:

• I have managed to identify the row number of a selected sector by using MATCH.
• I can get the names of the people with experience in the selected sector by using INDEX/SMALL but I have to manually enter the column range (shown in red below) associated with the "selected row"identified above into the array formula. How can I get the array to use the "MATCH"ed row automatically.
• array formula as follows: {=IF(ROWS($V8:$V$8)>K$7,"",INDEX($C$2:$G$2,SMALL(IF($C$10:$G$10=1,COLUMN($C$10:$G$10)-COLUMN($C$10)+1),ROWS($K8:$V$8))))}

 

[Aladin Akyurek]

Care to post a scaled-down sample, 5 row x 5 column, for example, and the expected result for that sample?

 

[bryce2563]

PERSONNEL SECTOR COMPETENCIES

Sector

Subsector

John

Mary

Jack

Tom

Betty

Total in sector

Select sector:

Sector 3

INPUT SECTOR FROM DROPDOWN LIST

Sector 1

0

0

1

1

0

2

Select subsector:

Sub 1

aaa

a

Sub 2

aa

a

Row number obtained from matching K2 from col A

Sector 2

1

1

1

0

1

4

Confirm Sector Row

10

Sub 3

aa

aa

Count of People

4

Sub 4

a

aa

a

aa

People with experience

John

Sub 5

aaa

aa

Mary

Sector 3

1

1

0

1

1

4

Tom

Sub 6

a

aa

Betty

Sub 7

a

aa

aa

Sub 8

a

aa

aa

Sector 4

1

0

1

0

1

3

Sub 9

a

aa

Ideal Output:

Sub 10

a

aa

aaa

Sector:

Sub 11

aa

Subsector:

Experience level

Sub 12

aa

People with experience

person 1

a

hyperlink to CV

person 2

aaa

hyperlink to CV

person 3

aa

hyperlink to CV

People with experience John, Mary etc are column headers obtained from using array with INDEX and SMALL functions. =IF(ROWS($V$7:$V7)>V$6,"",INDEX($E$1:$L$1,SMALL(IF($E$31:$L$31=1,COLUMN($E$31:$L$31)-COLUMN($E$31)+1),ROWS($V$7:$V7)))) How do I get the row number above automatically into the array ?

<tbody>
</tbody>

Table on the left is the data. Elements on the right are the inputs (sector from dropdown list) and outputs wanted. Sorry not very competent with inserting diagrams