Index/Match Formula for Date Range

**Brawnystaff** · 09-18-2015, 01:19 AM

See attached Excel spreadsheet with some sample data I made up. Sheet labeled "Single to Single Date Match" shows an Index/Match formula in cells E2:E5 that matches dates of service from Group 1 to Group 2 on and returns the invoice number for Group 2. I am trying to do a similar formula for sheet labeled "Single to Multi-Match", but not sure how to modify the formula, as I am trying to match up a single date specified in Column H to a date range specified in Columns B and C (e.g. if date in Column H falls between the to and from dates, then return the invoice number). Any ideas? Thanks.

**benishiryo** · 09-18-2015, 01:39 AM

hi there. maybe this array formula?
=IFERROR(INDEX($J$2:$J$5,MATCH(1,($H$2:$H$5>=B2)*($H$2:$H$5<=C2)*($I$2:$I$5=D2),0)),"")

**NeedForExcel** · 09-18-2015, 01:41 AM

But what if there are multiple dates that lie in a date range?

**Brawnystaff** · 09-18-2015, 01:43 AM

Thanks benishiryo. That worked.

**Brawnystaff** · 09-18-2015, 01:52 AM

Actually, good point Need for Excel..I modified the spreadsheet for multiple dates that lie in the range and nothing came back. Is there a way to get all dates in a range (assuming it had multiple dates)?

**NeedForExcel** · 09-18-2015, 01:52 AM

So if there are multiple dates, how would you want then to be displayed in a cell?

**Brawnystaff** · 09-18-2015, 01:56 AM

Another good point..probably the first invoice number it found..

**NeedForExcel** · 09-18-2015, 02:01 AM

In that case, benishiryo's formula should work or you.

Maybe you have not noticed, but Ben says its an array formula..

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**Brawnystaff** · 09-18-2015, 02:08 AM

Will do. Thanks.

Index/Match Formula for Date Range

LinkBack

Thread Tools

Rate This Thread

Display

Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Re: Index/Match Formula for Date Range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] index match with sumif date range

Index match then returning a range of date values

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

[SOLVED] Index Match with date range criteria

Index match, within a certain date range

Bookmarks

Bookmarks

Posting Permissions