INDEX Array Formula with multiple criteria? possible?

Hi all,

I am currently using the current array formula to bring dates of meetings in from another sheet within the same workbook.

=IF(ISERROR(INDEX(MEETINGS,SMALL(IF($BG$827=MEETINGADVISERKEY,ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!H1)),COLUMN(Meetings!H1))),"",INDEX(MEETINGS,SMALL(IF($BG$827=MEETINGADVISERKEY,ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!H1)),COLUMN(Meetings!H1)))


It works really well matching the adviser who attended the meeting, I would like to add a limiting date range in there as well and attempted this with the following code:

=IF(ISERROR(INDEX(MEETINGS,SMALL(IF(AND($BG$827=MEETINGADVISERKEY,MEETINGDATEKEY>=MSDATE,MEETINGDATEKEY<=MSDATE),ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!H1)),COLUMN(Meetings!H1))),"",INDEX(MEETINGS,SMALL(IF(AND($BG$827=MEETINGADVISERKEY,MEETINGDATEKEY>=MSDATE,MEETINGDATEKE<=MSDATE),ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!H1)),COLUMN(Meetings!H1)))

using an If(and, it lets me enter the code with no error message but brings back no results, for information MS & ME DATE are formatted as a short date.

Not sure where to go, any help would be greatly appreciated.

Cheers

thanks for the replies but I'm struggling to get either to work, not sure what i'm leaving out?

using this formula

=IF(ISERROR(INDEX(MEETINGS,SMALL(IF(($BG$1322=MEETINGADVISERKEY)*(MEETINGDATEKEY>=MSDATE)*(MEETINGDATE<=MEDATE),ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!H1)),COLUMN(Meetings!H1))),"",INDEX(MEETINGS,SMALL(IF(($BG$1322=MEETINGADVISERKEY)*(MEETINGDATEKEY>=MSDATE)*(MEETINGDATE<=MEDATE),ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!H1)),COLUMN(Meetings!H1)))

---------- Post added at 10:35 PM ---------- Previous post was at 10:29 PM ----------

=IFERROR(INDEX(MEETINGS,SMALL(IF(MEETINGADVISERKEY=$BG$1322,IF(MEETINGDATEKEY>=MSDATE,IF(MEETINGDATE<=MEDATE,ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""))),ROW(Meetings!H1)),COLUMN(Meetings!H1)),"")

also tried this but doesn't return any results which leades me to think maybe i need to include the date formula?

still nothing with this:

=IFERROR(INDEX(MEETINGS,SMALL(IF(MEETINGADVISERKEY=$BG$1322,IF(MEETINGDATEKEY>DATE(YEAR(MSDATE),MONTH(MSDATE),DAY(MSDATE)),IF(MEETINGDATE<DATE(YEAR(MEDATE),MONTH(MEDATE),DAY(MEDATE)),ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""))),ROW(Meetings!H1)),COLUMN(Meetings!H1)),"")

---------- Post added at 10:39 PM ---------- Previous post was at 10:38 PM ----------

yeh meetings is a multicolumn range, i use this formula to build statements that show the meetings held for the month, ie in the adjacent row i use:

=IF(ISERROR(INDEX(MEETINGS,SMALL(IF($BG$1322=MEETINGADVISERKEY,ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!C1)),COLUMN(Meetings!C1))),"",INDEX(MEETINGS,SMALL(IF($BG$1322=MEETINGADVISERKEY,ROW(MEETINGADVISERKEY)-MIN(ROW(MEETINGADVISERKEY))+1,""),ROW(Meetings!C1)),COLUMN(Meetings!C1)))

which brings through the clients name from the meeting.

---------- Post added at 10:40 PM ---------- Previous post was at 10:39 PM ----------

and drag down

appreciate the help I have attached a stripped out version effectively shows the same data just with all sensitive stuff and code taken out.

Cheers

apologies forgot them please see attached.

Cheers

amazing, thank you very much

@ a.mack123

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.