+ Reply to Thread
Results 1 to 6 of 6

Formula to evaluate two criteria and pull data if both met

  1. #1
    Registered User
    Join Date
    08-20-2020
    Location
    Nebraska
    MS-Off Ver
    MS Office Pro Plus 2016
    Posts
    5

    Formula to evaluate two criteria and pull data if both met

    Hello,

    Thanks for looking at this. I will provide a bit of context and then the specific request. I have a formula that needs to be updated. It is an IF formula where the evaluation and if true reference another sheet. I am looking at updating the if false part of that formula. It would also be beneficial if the entire formula could be wrapped in an IFERROR so that the cell does not show an error message if the criteria are false. I have tried to make this work using VLOOKUP and AND. I understand another combination of formulas to use is INDEX and MATCH, but I could not get these to work.

    I have provided a dummy file with several tabs, some needed and some just for context.

    Specific Request:

    I would like the cell in Printable Schedule C4 be updated so the if false part of the formula evaluate two criteria. The first is the name in A4 be evaluated with the range F3:F36 on Scheduling Input. The second criteria is the shift in B4 be compared with the shift in R3:R36 on Scheduling Input. Doing it this way will allow me to drag the formulas down for all 90 rows as it requires both criteria to be correct.

    Another way to do this would be just compare one criteria, the name, with F3:F8 and then pull the shift start time over. Setting it up this way would not allow me to drag the formulas down as the three shifts are show by person, rather than by shift.

    Ideally, I just want this to work correctly. If there is an easier way of doing it, that would be great.

    Thanks for your help with this. If I need to better explain anything, just let me know.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Formula to evaluate two criteria and pull data if both met

    Please select cell C4, paste the following into the formula bar and then drag the fill handle down to cell C93:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Similarly select cell D4, paste the following into the formula bar and then drag the fill handle down to cell D93:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

    EDIT: For the formulas to work unmerge the cells in the Server Name column (Printable Schedule sheet) and fill in the blank cells.
    Last edited by JeteMc; 08-30-2020 at 11:18 PM. Reason: edited text
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-20-2020
    Location
    Nebraska
    MS-Off Ver
    MS Office Pro Plus 2016
    Posts
    5

    Re: Formula to evaluate two criteria and pull data if both met

    Thank you for the solution. I put these into the cells you directed me to and dragged them down. They worked great on those two columns. I copied and pasted them into the remaining columns for that first week (through Saturday) and then edited the "$F$3:$F$36" to the appropriate letter. After doing this and dragging down, this entire first week was complete. After this, I then moved on to the next week. Here is where I ran into a problem. I altered the formulas so they were pulling data from rows 42-75 rather than 3-36. Unfortunately, this is where the results weren't matching with what was on the Scheduling Input sheet. There are two parts of the formula that I don't understand, so I am guessing it is one of those. They are the "AGGREGATE(15,6" part and the "'Scheduling INPUT'!$B$2" part. I altered the B2 to reflect B40, but that didn't fix it.

    You help on this is greatly appreciated. Thanks.
    Last edited by MDavid1; 08-31-2020 at 05:54 PM. Reason: Wording fix

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Formula to evaluate two criteria and pull data if both met

    Try the following in Q4 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As for AGGREGATE(15,6 The 15 is the SMALL function and 6 is an option to ignore errors. So the formula will find the smallest (note that K is 1) value having meeting the criteria of name and shift.
    -ROW('Scheduling INPUT'!$B$41) modifies the array produced by ROW('Scheduling INPUT'!$B$42:$B$75) by subtracting 41 from each value in the array.
    I suggest utilizing the Evaluate Formula feature (Formulas tab) to see how the formula works.
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    08-20-2020
    Location
    Nebraska
    MS-Off Ver
    MS Office Pro Plus 2016
    Posts
    5

    Re: Formula to evaluate two criteria and pull data if both met

    JeteMc,

    This worked great. And thanks for the explanation. I think I understand how it is working now. I was able to make the changes when going to the later weeks and it worked beautifully. I really appreciate your help with this.

    Thanks.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Formula to evaluate two criteria and pull data if both met

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. Complex Index/match formula to pull data based on 1 criteria. (Bid/Cost Sheet)
    By Blackmagic42 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-18-2019, 01:45 AM
  2. Formula to pull data when multiple criteria are met.
    By mike rand in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-30-2018, 08:31 AM
  3. [SOLVED] Formula to evaluate criteria across two matrices
    By The_Snook in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-03-2018, 04:47 AM
  4. [SOLVED] Formula to evaluate criteria across two matrices
    By The_Snook in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-28-2018, 03:56 AM
  5. [SOLVED] Pull data from a table based on multiple criteria - old formula trying to use AND
    By tweaver in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2016, 04:37 PM
  6. Formula to pull rows of data based on a column cell value (Criteria)
    By PaulLor89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2013, 11:26 AM
  7. Replies: 6
    Last Post: 08-09-2011, 10:25 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