Adding AND into my formula

[eye_in_the_sky]

I have a spreadsheet where I use the following array formula to search a single column and return all the corresponding values in a different column (column 2).

=INDEX(A1:F5000,SMALL(IF(D1:D5000=J2,ROW(D1:D5000)),ROW(1:1)),2)

Is there a way to use AND to add a second criteria where E1:E5000 must = L2??

I want it to search two columns for two different values, and return the corresponding value in the rows where both conditions are met.

Help... please and thank you!!!

 

[CSyx]

In order to use the AND in your function try multiplying the two requirements...=INDEX(A1:F5000,SMALL(IF((D1:D5000=J2)*(E1:E5000=L2),ROW(D1:D5000)),ROW(1:1)),2)

 

[eye_in_the_sky]

In order to use the AND in your function try multiplying the two requirements...=INDEX(A1:F5000,SMALL(IF((D1:D5000=J2)*(E1:E5000=L2),ROW(D1:D5000)),ROW(1:1)),2)

IT WORKS!!!!!!!!!
Thank you SOOOO much!!!!
I tried this earlier but I must have typed it wrong (no surprise there) because it returned an error. This site rocks!