HI!

I am using this formula to look for 1 criteria, but I was wondering If it can be made to look for more that one. I uploaded an example.
In the example I added the other criteria I want to look for


=IF(ISERROR(INDEX($A$1:$B$14,SMALL(IF($A$1:$A$14=$ E$1,ROW($A$1:$A$14)),ROW(1:1)),2)),"",INDEX($A$1:$ B$14,SMALL(IF($A$1:$A$14=$E$1,ROW($A$1:$A$14)),ROW (1:1)),2))

Thanks!

Attached Files

Copy of eImagine-Excel-Multiple-Matches.xlsx (16.4 KB, 25 views)

Other than changing the two references to $E$1 to E$1, so you can copy the formula across, I'm not sure what you're after.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

And if you are using a post 2003 version as you indicate in your info, you can replace (If(iserror with IFERROR

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I am sorry if I was not clear enough.
If you look at the example, the formula works by looking 1 criteria for example "mouse" but I need to look in the same column for " mouse, dog, cat, sheep and horse" (or whatever 5 I put). I want to return all the formula finds of those 5 in a column (e1:e20 for example) instead of having to copy the formula across for each one of the items I need to find as you understood .

In that case, you could change:
$E$1
to:
$E$1:$I$1

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Oh, my God! I tried that before and since it did not work I decided to ask in the forum. I just realized it was because of the control+shift thing. Urrg!


Thanks a lot of your help if it weren't for all the help I find in this forum I would have gone crazy by now.