Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match

Good afternoon all!

So i have three formulas that took awhile to put together but i cant seem think of a way to combine them. Each one works fine separately. So i am looking for two things to be answered....1: how can i combine these formulas into so i can drag them down and 2: is my formula more complicated than it needs to be?

Basically all three are the same just with slightly different logical statements. I have a 4 week rolling sheet where you select a certain date and then it will auto fill all the people that meet the criteria for each day. As the formula is dragged down the "Row(1:1)" increases by 1:1 so that it will return the first, second, third...etc. name that meets the criteria. I have one sheet that is a calendar with peoples names in Column A and then each day for the year are in columns H:PO.

The offset formula is looking to check the first column for "A" and then the second column for "T". Nov_30 references "='2014 Calendar'!$H$11:$H$92", the first column of the calendar. Then VLOOKUP is looking at a separate sheet that houses the Dates and what column it is in. For example:

8/14/2014 257 258 259

8/14/2014 starts in column 257 and we check for A and then checks for T in column 258. In this example we would only look for column 3 in the third equation which looks for 3 criteria.I couldnt think of a way for excel to look at each column without setting up the separate sheet with a ton of dates and column numbers and then OFFSETting them by how many columns away it is from the first column H:H.

So i am not sure if this is too complicated to type out so if you need an example please let me know. Thanks!

IFERROR(INDEX('2014 Calendar'!$A$11:$PO$92,SMALL(IF(OFFSET(Nov_30,0,VLOOKUP('4 Week Rolling'!D$4,'DO NOT EDIT-4 Week Rolling'!$A$2:$C$425,2,FALSE))="A",IF(OFFSET(Nov_30,0,VLOOKUP('4 Week Rolling'!D$4,'DO NOT EDIT-4 Week Rolling'!$A$2:$C$425,3,FALSE))="T",ROW('4 Week Rolling'!$KJ$12:$KJ$93))),ROW('4 Week Rolling'!1:1))-11,1),"")

IFERROR(INDEX('2014 Calendar'!$A$11:$PO$92,SMALL(IF(OFFSET(Nov_30,0,VLOOKUP('4 Week Rolling'!A$4,'DO NOT EDIT-4 Week Rolling'!$A$2:$C$425,2,FALSE))="A",IF(LEN(OFFSET(Nov_30,0,VLOOKUP('4 Week Rolling'!A$4,'DO NOT EDIT-4 Week Rolling'!$A$2:$C$425,3,FALSE)))=2,ROW('4 Week Rolling'!$KJ$12:$KJ$93))),ROW('4 Week Rolling'!1:1))-11,1),"")

IFERROR(INDEX('2014 Calendar'!$A$11:$PO$92,SMALL(IF(OFFSET(Nov_30,0,VLOOKUP('4 Week Rolling'!A$4,'DO NOT EDIT-4 Week Rolling'!$A$2:$C$425,2,FALSE))="T",IF(OFFSET(Nov_30,0,VLOOKUP('4 Week Rolling'!A$4,'DO NOT EDIT-4 Week Rolling'!$A$2:$C$425,3,FALSE))="A",IF(LEN(OFFSET(Nov_30,0,VLOOKUP('4 Week Rolling'!A$4,'DO NOT EDIT-4 Week Rolling'!$A$2:$D$425,4,FALSE)))=2,ROW('4 Week Rolling'!$KJ$12:$KJ$93)))),ROW('4 Week Rolling'!1:1))-11,1),"")

So i added an example workbook of what i was trying to do hopefully it makes more sense.Example.xlsx

I may have saved it with manual calculations. On mine it pulls the correct numbers from the calendar sheet.

Outputs.PNG

Awesome thanks for checking this out. I was dying trying to figure out how to combine these...

Correct but that is only one of the formulas. There is one that looks for T then A then 2 digit state code. And another formula that looks for A and then 2 digit state code. I posted all the formulas in the first post and also column A on the 4 week rolling sheet. They all work on their own but im trying to combine them together into one formula if possible.

So thats the thing there are none really they can be all intermixed. Peoples names could be pulled in any order....2 people with A then two digit.....1 with T then A then two digit....then another A with two digit. It doesnt matter the order just that it pulls all names that match those criteria and list them.

Hmmm sounds about right I was thinking of using the OR function but couldn't figure it out I never use it. I'm stuck n rush hour traffic but I'll try it out tonight. Thanks

Awesome I'll try that when I get home. In regards to offsetting one column by use of the helper sheet. Anyway to make that cleaner or is what I did a decent method?

Hey i just put it in my master workbook and it worked perfectly. I thought i messed it up because i put the formula in the first column and it should have been looking at the date in column C but it was looking at E. I thought i messed it up but i made the quick change and now its great haha. Thanks so much. Now i just need to try to understand what the formula is doing...

Wow i completely missed that sheet the first time. Thanks so much for making that im reading through it right now.

I never knew that + = or and * = and.....that is gonna be a huge help in the future. Thanks so much again for doing this.