producing a report via a keyword - filtering and macros not suitab

[UKMAN]

help needed please........ I am not a formula expert so bear with me please.
I know filtering is available but I am trying to save effort as data goes
into a seperate report.

Basically I have a skill matrix spread worksheet I need to extract data from
using a keyword search so:

the dbase sheet is designed as:

Col a Col B through to Col Z
Row 1 Skill title i.e. Excel
Row 2 Name Knowledge level between 1 to 5 i.e. 2

Note there is about 100 rows of names.

example therefore is:
Excel word
Colin 2 3
Sarah 1 5
Pete 0 3

What I need to do is on a seperate worksheet is from a drop down list i.e
a1, select a title (listing the titles from col b through to col z)

in b3 and c3 downwards, List the names and that persons knowledge value i.e.

Drop down selection is [excel]

return in the report is
Colin 2
Sarah 1

You notice Pete is not reported on.

Can anyone help please......

Cheers
UKMAN1

 

[Steve Dunn]

Hi

Array Entered* in B3:

=INDEX(dbase!$A$2:$Z$100,SMALL(IF(OFFSET(
dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,
1)<>0,ROW(dbase!$A$2:$Z$100)-CELL("ROW",
dbase!$A$2:$Z$100)+1),ROW()-ROW($B$3)+1),1)


Array Entered in C3:

=INDEX(dbase!$A$2:$Z$100,SMALL(IF(OFFSET(
dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,
1)<>0,ROW(dbase!$A$2:$Z$100)-CELL("ROW",
dbase!$A$2:$Z$100)+1),ROW()-ROW($B$3)+1),
MATCH($A$1,dbase!$B$1:$Z$1,0)+1)


Copy B3:C3 down as far as required.

It is possible to do this without Array Entering, but the formulae become
twice as long...

In B3:

=INDEX(dbase!$A$2:$Z$100,SMALL(INDEX((OFFSET(dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,1)<>0)*(ROW(dbase!$A$2:$Z$100)-CELL("ROW",dbase!$A$2:$Z$100)+1),),COUNTIF(OFFSET(dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,1),0)+COUNTIF(OFFSET(dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,1),"")+ROW()-ROW($B$3)+1),1)

in C3:

=INDEX(dbase!$A$2:$Z$100,SMALL(INDEX((OFFSET(
dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,1)
<>0)*(ROW(dbase!$A$2:$Z$100)-CELL("ROW",
dbase!$A$2:$Z$100)+1),),COUNTIF(OFFSET(
dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,
1),0)+COUNTIF(OFFSET(dbase!$A$2:$Z$100,,MATCH($A$1,
dbase!$B$1:$Z$1,0),,1),"")+ROW()-ROW($B$3)+1),
MATCH($A$1,dbase!$B$1:$Z$1,0)+1)


*After typing the formula use Ctrl+Shift+Enter instead of just Enter.

HTH
Steve D.