Lookup and show multiple values in new cells?

[happydays886]

Hi All,

I'm not sure if this is possible, but I'll ask.

I have a file (attached) that shows different types of FELINE and CANINE as categories. I'd like to be able to show, in the highlighted areas, each animal that has an amount within a specified range in a corresponding cell. If that animal has already been identified/selected, to then identify the next animal and so-fourth.

Thanks for your help!

[Glenn Kennedy]

Can I ask you to re-check your cut-off criteria again?

One way (feline) is to use a separate hard-coded formula for each row, using variants of this (different shading = different formula):
=IFERROR(INDEX($F:$F,AGGREGATE(15,6,ROW($F$6:$F$17)/(($E$6:$E$17=$K$5)*($G$6:$G$17< =5)),COLUMNS($L:L))),"")

However, that might be a pain to set up if you have lots of ranges, and not just 3. An alternative (done for canine) is to re-jig teh layout a bit and use one formula, copied across and down:

=IFERROR(INDEX($F:$F,AGGREGATE(15,6,ROW($F$6:$F$17)/(($E$6:$E$17=$K$10)*($G$6:$G$17> =$J12)*($G$6:$G$17< IF($K12="",10^300*$G$6:$G$17,$K12))),COLUMNS($L:L))),"")

If you like the second option, you do need to check EXACTLY what your cut-off criteria are....