+ Reply to Thread
Results 1 to 21 of 21

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

  1. #1
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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)
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #5
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #7
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #9
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #10
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #11
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #12
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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.
    Last edited by daffodil11; 09-25-2014 at 06:18 PM.

  13. #13
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #14
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #15
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #16
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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".
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #18
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #19
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #20
    Registered User
    Join Date
    03-04-2014
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2010
    Posts
    37

    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. #21
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

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

    No problem. Glad I could help out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. [SOLVED] INDEX MATCH trying to return a value between two criteria.
    By cyberkidkiller in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2014, 09:48 AM
  3. [SOLVED] Using Index Match with a Criteria to return a minimum
    By david0985 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2014, 12:59 PM
  4. Index Match using two criteria to return on of criteria values
    By RogueLeader in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 09:50 AM
  5. Replies: 1
    Last Post: 11-06-2013, 08:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1