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 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))))}