INDEX MATCH based on Date Range and Name

**treegee** · 06-13-2019, 05:48 PM

My goal is to find an average score for a given agent based on a date range.

I have been using the following formula:

=ArrayFormula(AVERAGE(INDEX(Sheet1!$BJ:$BJ,MATCH(1,((Sheet1!$E:$E>=$F$5)*(Sheet1!$E:$E<=$F$6)*(Sheet1!$B:$B=$F$7)),0))))

whereas Sheet1!BJ:BJ is the index from which the QA scores are pulled if it matches the name determined from the dropdown F7 AND falls within the date range (start date F5 and end date F6).

However, my formula appears to be dependent only on the start date. How can I write the formula so that the results will yield ALL the entries within the date range for that agent?

Please advise, thank you!

**jason.b75** · 06-13-2019, 06:09 PM

Your formula appears to be for google sheets, not excel.

Try (excel formula).

=AVERAGEIFS(Sheet1!$BJ:$BJ,Sheet1!$E:$E,">="&$F$5,Sheet1!$E:$E,"<="&$F$6,Sheet1!$B:$B,$F$7)

If this does not work, I suggest that you ask a moderator to move your question to the correct sub-forum for google sheets questions. You can do this by clicking the 'report post' button on your question.

**treegee** · 06-13-2019, 06:14 PM

YES! THANK YOU! I was working in google sheets because the doc must be filled in live-time... but your equation worked! Thank you!!!

**jason.b75** · 06-13-2019, 06:27 PM

You're welcome! Thanks for the feedback.

For your information, index and match only return the first correct match from the array, not all correct matches. It appeared to only match the start date because presumably, your data is in chronological order. If you sorted it in reverse order, then it would have appeared to match only the end date because the latest entry would be first.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**treegee** · 06-14-2019, 11:58 AM

Thank you for the feedback and help. I appreciate it.

INDEX MATCH based on Date Range and Name

LinkBack

Thread Tools

Rate This Thread

Display

INDEX MATCH based on Date Range and Name

Re: INDEX MATCH based on Date Range and Name

Re: INDEX MATCH based on Date Range and Name

Re: INDEX MATCH based on Date Range and Name

Re: INDEX MATCH based on Date Range and Name

Thread Information

Users Browsing this Thread

Similar Threads

Index Match within specified date range

Index Match to find match date to a range of dates

IF statement that also uses index match to verify if date falls with date range

[SOLVED] Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date

[SOLVED] INDEX date that MATCH on date range, MATCH code(string)

[SOLVED] Index Match Based on date range and name criteria

[SOLVED] INDEX MATCH based on 5 criteria, including a date range.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions