# Formula to evaluate two criteria and pull data if both met

1. ## 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.  Register To Reply

2. ## 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:  `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:  `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.  Register To Reply

3. ## 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.  Register To Reply

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

Try the following in Q4 and down:
Formula:  `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.  Register To Reply

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.  Register To Reply

6. ## 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.  Register To Reply

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