problem with INDEX([...],SMALL(IF[...],ROW to list results based on more than one criteria

Hi all,

I'm new to the INDEX formula (and also new to this forum) and I'm having an issue trying to list matches for more than one criteria. I have a working formula of:
{=INDEX(Data001!$A$1:$C$101,SMALL(IF(Data001!$B$1:$B$101=$E$6,ROW(Data001!$B$1:$B$101)),ROW(1:1)),1)}

which allows me to choose a name from a dropdown (E6) and it returns every report number that person has done. I'm trying to have it also filter the results and show every instance of the person's name, within the pay period range. I tried an IF(AND( but that didn't work, I tired an IF([...],IF([...], but I must be doing something wrong with the syntax. I'd like to have to formula be:
{=INDEX(Data001!$A$1:$C$101,SMALL(IF(Data001!$B$1:$B$101=$E$6,IF(Data001!$C$1:$C$101>$E$2,IF(Data001!$C$1:$C$101<$F$2,ROW(Data001!$B$1:$B$101)))),ROW(1:1)),1)} but it returns a#NUM! error. Can someone help?

Any suggestions? I've attached a sample.

Try this in A2 =IFERROR(INDEX(Data001!$A$1:$C$101,SMALL(IF(Data001!$B$1:$B$101=$E$6,ROW(Data001!$B$1:$B$101)),ROW(1:1)),1),"") and drag down till A100

=IFERROR(VLOOKUP(A2,Data001!A:B,2,FALSE),"") in B2 drag down till B100
=IFERROR(VLOOKUP(A2,Data001!A:C,3,FALSE),"") in C2 and drag down Till C100.

Why upto 100 rows down ? Check your data with fred it has more info than 20 rows

Hi,

Actually your attempt was very nearly correct. However:

1) There are no records matching those date criteria for Diane!! (Hence the #NUM! error)

2) ROWS($1:1) is preferable to ROW(1:1) as the parameter in the SMALL function as it is not susceptible to row insertion.

All in all, (again, array-entered) in A2:

=IFERROR(INDEX(Data001!$A$1:$A$101,SMALL(IF(Data001!$B$1:$B$101=$E$6,IF(Data001!$C$1:$C$101>$E$2,IF(Data001!$C$1:$C$101<$F$2,ROW(Data001!$B$1:$B$101)))),ROWS($1:1))),"")

Regards

Originally Posted by XOR LX

=IFERROR(INDEX(Data001!$A$1:$A$101,SMALL(IF(Data001!$B$1:$B$101=$E$6,IF(Data001!$C$1:$C$101>$E$2,IF(Data001!$C$1:$C$101<$F$2,ROW(Data001!$B$1:$B$101)))),ROWS($1:1))),"")

Thank you for this- it gives the desired results! I guess I've been misunderstanding a couple details about the array formula. I'll be applying this to my production spreadsheet which references a table of 50,000 rows and has previously been a pain to sort out!

I'm confused by the "*" between the IF statements in as well, in kvsrinivasamurthy's post, is that a wildcard or to multiply? The formula didn't work for me, I did get a working version from XOR LX though.

Thank you all for the assistance!

You're welcome.