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

1. 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),"")

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

It's difficult to guess without seeing your attached source material, but I can't help but feel as though this could have been shortened considerably through the application of nested expressions within a standard LOOKUP.

=IFERROR(LOOKUP(2,1/CRITERIA CRITERIA CRITERIA,2014 Calendar'!\$A\$11:\$PO\$92)

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

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

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

None of the formulas appear to be producing any outputs. Is that intended?

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

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

Outputs.PNG

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

Ok, cool. Reverse engineering in progress.

The nested If can definitely be shorted to just two expressions times each other greater than 0. I'm going to try nesting a few indices within another.

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

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

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

Just so I'm understanding this correctly, it's just attempting to pull Column A where the date matches A's, and date + 1 matches T's?

And then pull down to list them all?

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

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.

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

Ok, so why one and not the other? Which output takes precedence?

If error, output next? Formula1, IfError Output2, IfError Output3?

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

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.

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

So, think of all three formulas as different criteria expressions, and index all just the names that match any of them?

Of 1, 2, 3, 4 you have expression1: >3, expression2: <2, and expression3: =3. Of those 4 numbers you want: 1, 3, 4. Any and all that match any of the three equations.

Sounds like maybe an array version of the OR function.

We add criteria expressions together, then find out which are > 0.

Using the example above:

(((1;2;3;4>3)+(1;2;3;4<2)+(1;2;3;4=3))>0)
(1,0,1,1)>0
And now we have an array that match 1, 3, 4.

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

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

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

When working with arrays, OR usually doesn't get it done. Instead we use the additive method above to evaluate for any condition.

I worked through it and got it to work using the same logic I outlined previously.
Note, in circumstances when your formulas start getting heavy and complicated you should by all means be using Alt+Enter to apply line breaks to keep your formula organized.

I entered this formula in E6 and copied down.

``Please Login or Register  to view this content.``

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

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?

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

I think it works pretty well.

I'll stew it over on the long commute home. I usually do this for an hour or two to avoid most of the rush hour traffic.

Also, attached your example with the formulas filled out and a brief tutorial on the array version of "OR".

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

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...

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

Did you read my attachment? The tutorial explains it fairly well.

You have three conditions you're checking for. The use of the OR method is checking if records qualify against any of the three.

(expression1 + expression2 + expression3)>0

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

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

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

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

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

No problem. Glad I could help out.

There are currently 1 users browsing this thread. (0 members and 1 guests)