Creating a dynamic array to search for multiple specific entries based on criteria

[Travisty]

Hello everyone,

So before I begin, this site does not let me post posts that contain formula's. I have no idea why (It tells me I can't post HTML) and I apologize for any confusion it might cause. I've attached a workbook to help clear up any confusion.

I work for a company that runs different types of activities (rock climbing, trekking, etc). What I'm basically trying to do is create a a check in sheet so that we can easily see who has arrive and who we are still waiting on. I've attached a worksheet below to help explain, but basically, I'm using "formula 1" in the work book to search for entries.

What it does is search through our sales sheet looking for any one who is going on any of our trips on a specific date. For example, if it see LHB-HD-RC-OT on a date it lists that person's name under "Rock Climbing" so that we know to expect them.

Where I am having trouble is that I want it to be able to search for multiple different entries in the sales sheet and list them under the same heading. For example, under the "Rock Climbing" heading I want it to list people under the Rock Climbing heading who are doing a half day of rock climbing (LHB-HD-RC-OT) OR people who are doing a full day of rock climbing (LHB-FD-RC-OT) since they will arrive at the same time. I've tried "formula 2" from the workbook, where I tell excel to look for these extra entries, but the formula doesn't seem to work. I think it's because it's looking for two different entries in the same column and is getting confused.

Does anyone know how I can manipulate this formula to achieve my desired result?

[JohnTopley]

Try this in C3

=IFERROR(INDEX('Walk in Sales'!$D$3:$D$9,SMALL(IF(('Walk in Sales'!$E$3:$E$9='Check In'!$A$2)+('Walk in Sales'!$E$3:$E$9='Check In'!$A$3)*('Walk in Sales'!$C$3:$C$9='Check In'!$D$1),ROW($A$3:$A$9)-ROW($A$3)+1,22),ROWS($A$3:A3))),"")

Enter with Ctrl+Shift+Enter

Copy down

You will need change the highlighted Criteria for the codes for each activity

[Glenn Kennedy]

I'm a bit slow this morning...

Please Login or Register  to view this content.

also array entered in c3....

[Travisty]

Thanks a bunch guys!

John, your fomula almost worked, it just wasn't date specific (C3P0 and R2 who were going the next day still showed up)

Glenn, yours worked.

Thanks again!