multiple criteria search in EXCEL

Search for bathing time of monkey.PNG

Hi,

I have been scratching my head over this question.

I have a list of activities performed by animals that comes along with their start time. I am unable to change the orientation of the list and the activities they do are placed below their name (eating, bathing, swimming and sleeping).

I am trying to find a way to locate and state the bathing time for MONKEY. How do I let excel know where to find the bathing time given the criteria of "MONKEY" and "OUTDOOR"? I do not want it to look for the bathing time of other animals in this case "DONKEY"

PS: is it possible for excel to look 4 to 5 boxes below the BATCH ID of MONKEY to locate BATHING time given the criteria its also under the UNIT "OUTDOOR"? Will be glad if a formula can be used for all animals in my list!

Thank you if you are able to help!!

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

You have not yet responded to the help offered in your first thread here - please do so now.

Assuming the sub-headings are the same for all animals:

=INDEX($C$5:$C$14,MATCH(F5,$A$5:$A$14,0)*MATCH(SUBSTITUTE(H4,"START TIME OF ",""),$A$6:$A$9,0))

If not, then you will need to add another column to the table for the animal and fill it down.

Not if you change the lookup value.

I think the problem is that your sample data isn’t telling the whole story. If you are wanting multiple lines returned, then you need to show this in your sample workbook.