Extracting rows/data according to certain criteria from input control

[taviz]

Hello everybody,

I have a huge data set from which I need to find and elaborate results.
In this example I need to identify people who have a combination of certain skills (value 0-5) and certifications(Yes if they have it). I have to cross match with lookup every time and I want to automate it.
Suppose I need to find all the people who have a certain skill level e.g. Excel skill level 4 or 5 and they must have Prince certification. I want to have a way, maybe with some automatic controls that give me a drop down list to select a skill and certification and then elaborate in a new sheet a table that helps me identify the people who have that combination. OR also a combination of skills with certain level.

Thanks a lot !!
Tavi

[crazyforexcel]

Hi,

I am not that familiar with pivot tables but maybe this array formula can achieve your desired results:

In cell G8:

=IFERROR(INDEX($B$3:$F$14, SMALL(IF(COUNTIF($I$3, $D$3:$D$14)*COUNTIF($I$4, $E$3:$E$14)*COUNTIF($I$5,$F$3:$F$14), ROW($B$3:$F$14)-MIN(ROW($B$3:$F$14))+1), ROW(A1)), COLUMN(A1)),"")



CTRL+SHIFT+ENTER to make it an array then drag across and down

I hope this helps

PS I went a few steps further with Rev 1 (macro workbook). You can search by 2 criteria, 3 criteria, or by name and list all of their skills.