Using vlookup to check a date against a date range and return a value

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
87
Office Version
  1. 2019
Platform
  1. Windows
I have a table of schools and dates that they played a basketball game.

[TABLE="width: 500"]
<tbody>[TR]
[TD]School[/TD]
[TD]Game date[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]12/1/1987[/TD]
[/TR]
[TR]
[TD]Virginia[/TD]
[TD]12/24/1987[/TD]
[/TR]
[TR]
[TD]Virginia[/TD]
[TD]1/10/1988[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]11/27/1988[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]11/30/1988[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]12/22/1988[/TD]
[/TR]
</tbody>[/TABLE]

I have another table that gives all of the teams that were ranked in the Top 20 and a date range for when the ranks were in effect:

[TABLE="width: 500"]
<tbody>[TR]
[TD]School[/TD]
[TD]Ranking[/TD]
[TD]Rank start[/TD]
[TD]Rank end[/TD]
[/TR]
[TR]
[TD]Duke[/TD]
[TD]3[/TD]
[TD]11/6/1987[/TD]
[TD]11/13/1987[/TD]
[/TR]
[TR]
[TD]Duke[/TD]
[TD]6[/TD]
[TD]10/31/1988[/TD]
[TD]11/6/1988[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]15[/TD]
[TD]11/20/1988
[/TD]
[TD]11/27/1988
[/TD]
[/TR]
[TR]
[TD]North Carolina
[/TD]
[TD]8[/TD]
[TD]1/15/1989[/TD]
[TD]1/22/1989[/TD]
[/TR]
[TR]
[TD]Syracuse[/TD]
[TD]12[/TD]
[TD]3/1/1988[/TD]
[TD]3/8/1988[/TD]
[/TR]
</tbody>[/TABLE]

Sometimes a school will not have been ranked at all during a season.

How do I write a vlookup or a if/then that take the school name and the game date and returns a ranking if the school was ranked between the start and end dates?
 
Last edited:
Column A - school names of ranked teams
Column B - rankings
Column C - start date of ranking
Column D - end date of ranking
Column K - school name
Column L - game date

Here is the formula in the cell giving me 50:

=SUMPRODUCT(--($L268107>=$C$2:$C$24848),--($L268107<=$D$2:$D$24848),--($K268107=$A$2:$A$24848),$B$2:$B$24848)

When I use the INDEX formula, I get all blanks even when I enter it with ctrl-shift-enter.
Okay I worked on the Index formula and got it to give me some output. I am getting different output for some cells than what I'm getting from the sumproduct.

=IFERROR(INDEX($B$2:$B$24848,MATCH(K268107,IF($L268107>=$C$2:$C$24848,IF($L268107<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")

I get 25 (correct response) from the index and 50 from the Sumproduct.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Do you have any duplicates for ranking in columns A - D. This will cause wrong results with SUMPRODUCT formula (see example below - items in green).
The INDEX formula should just take the first item it finds, so duplicates should not matter.
Excel Workbook
ABCDEJKLMN
1SchoolRankingRank startRank endSchoolDateRankINDEX
2Duke311/6/198711/13/1987Oklahoma11/25/19883015
3Duke610/31/198811/6/1988Virginia12/24/19870
4Oklahoma1511/20/198811/27/1988Virginia1/10/19880
5North Carolina81/15/19891/22/1989Oklahoma11/27/19883015
6Oklahoma1511/20/198811/27/1988Oklahoma11/30/19880
7Duke53/1/19883/8/1988Oklahoma12/22/19880
8Duke3/5/198855
9Duke3/7/198855
10North Carolina1/20/198988
11Oklahoma11/26/19883015
Sheet
 
Upvote 0
Do you have any duplicates for ranking in columns A - D. This will cause wrong results with SUMPRODUCT formula (see example below - items in green).
The INDEX formula should just take the first item it finds, so duplicates should not matter.

ABCDEJKLMN
SchoolRankingRank startRank endSchoolDate
DukeOklahoma
DukeVirginia
OklahomaVirginia
North CarolinaOklahoma
OklahomaOklahoma
DukeOklahoma
Duke
Duke
North Carolina
Oklahoma

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Rank[/TD]
[TD="align: center"]INDEX[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]11/6/1987[/TD]
[TD="align: right"]11/13/1987[/TD]

[TD="align: right"]11/25/1988[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]10/31/1988[/TD]
[TD="align: right"]11/6/1988[/TD]

[TD="align: right"]12/24/1987[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]11/20/1988[/TD]
[TD="align: right"]11/27/1988[/TD]

[TD="align: right"]1/10/1988[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]1/15/1989[/TD]
[TD="align: right"]1/22/1989[/TD]

[TD="align: right"]11/27/1988[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]11/20/1988[/TD]
[TD="align: right"]11/27/1988[/TD]

[TD="align: right"]11/30/1988[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]3/1/1988[/TD]
[TD="align: right"]3/8/1988[/TD]

[TD="align: right"]12/22/1988[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3/5/1988[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3/7/1988[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1/20/1989[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]11/26/1988[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
M2=SUMPRODUCT(--($L2>=$C$2:$C$24848),--($L2<=$D$2:$D$24848),--($K2=$A$2:$A$24848),$B$2:$B$24848)
N2{=IFERROR(INDEX($B$2:$B$24848,MATCH($K2,IF($L2>=$C$2:$C$24848,IF($L2<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Hi! So I went through the mismatches and there were not any duplicate rows, BUT some of the date ranges were overlapping.

So I took care of those issues but found another.

2evx752.jpg


Both formulas are wrong here. The sumproduct is adding Michigan's and Michigan State's rankings together and the index formula is choosing Michigan State instead of Michigan.

Any idea why this is happening and how to fix it?
 
Last edited:
Upvote 0
Not sure. I couldn't duplicate the issue.
See example below it found the right school with both the SUMPRODUCT and INDEX formula.
I included all the formulas so maybe you will see a difference between them and your formula.
Excel Workbook
ABCDEJKLMN
1SchoolRankingRank startRank endSchoolDateRankINDEX
2Duke311/6/198711/13/1987Oklahoma11/25/19880 
3Duke610/31/198811/6/1988Virginia12/24/19870 
4Michigan1511/17/201411/23/2014Virginia1/10/19880 
5North Carolina81/15/19891/22/1989Michigan State11/20/20141010
6Michigan State1011/17/201411/23/2014Oklahoma11/30/19880 
7Duke53/1/19883/8/1988Oklahoma12/22/19880 
8Duke3/5/198855
9Duke3/7/198855
10North Carolina1/20/198988
11Michigan11/20/20141515
Sheet
 
Upvote 0
Not sure. I couldn't duplicate the issue.
See example below it found the right school with both the SUMPRODUCT and INDEX formula.
I included all the formulas so maybe you will see a difference between them and your formula.


ABCDEJKLMN


Duke

Oklahoma
Duke

Virginia


Virginia
North Carolina



Oklahoma
Duke

Oklahoma






Duke






Duke






North Carolina







<colgroup><col style="width:30px; "><col style="width:98px;"><col style="width:56px;"><col style="width:86px;"><col style="width:85px;"><col style="width:32px;"><col style="width:22px;"><col style="width:131px;"><col style="width:82px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]School[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Ranking[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Rank start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Rank end[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]School[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Rank[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]INDEX[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]11/6/1987[/TD]
[TD="align: right"]11/13/1987[/TD]

[TD="align: right"]11/25/1988[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]10/31/1988[/TD]
[TD="align: right"]11/6/1988[/TD]

[TD="align: right"]12/24/1987[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] "]Michigan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]11/17/2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]11/23/2014[/TD]

[TD="align: right"]1/10/1988[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]1/15/1989[/TD]
[TD="align: right"]1/22/1989[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] "]Michigan State[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]11/20/2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] "]Michigan State[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]11/17/2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]11/23/2014[/TD]

[TD="align: right"]11/30/1988[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]3/1/1988[/TD]
[TD="align: right"]3/8/1988[/TD]

[TD="align: right"]12/22/1988[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]3/5/1988[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]3/7/1988[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]1/20/1989[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] "]Michigan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]11/20/2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]15[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
M2=SUMPRODUCT(--($L2>=$C$2:$C$24848),--($L2<=$D$2:$D$24848),--($K2=$A$2:$A$24848),$B$2:$B$24848)
N2{=IFERROR(INDEX($B$2:$B$24848,MATCH($K2,IF($L2>=$C$2:$C$24848,IF($L2<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M3=SUMPRODUCT(--($L3>=$C$2:$C$24848),--($L3<=$D$2:$D$24848),--($K3=$A$2:$A$24848),$B$2:$B$24848)
N3{=IFERROR(INDEX($B$2:$B$24848,MATCH($K3,IF($L3>=$C$2:$C$24848,IF($L3<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M4=SUMPRODUCT(--($L4>=$C$2:$C$24848),--($L4<=$D$2:$D$24848),--($K4=$A$2:$A$24848),$B$2:$B$24848)
N4{=IFERROR(INDEX($B$2:$B$24848,MATCH($K4,IF($L4>=$C$2:$C$24848,IF($L4<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M5=SUMPRODUCT(--($L5>=$C$2:$C$24848),--($L5<=$D$2:$D$24848),--($K5=$A$2:$A$24848),$B$2:$B$24848)
N5{=IFERROR(INDEX($B$2:$B$24848,MATCH($K5,IF($L5>=$C$2:$C$24848,IF($L5<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M6=SUMPRODUCT(--($L6>=$C$2:$C$24848),--($L6<=$D$2:$D$24848),--($K6=$A$2:$A$24848),$B$2:$B$24848)
N6{=IFERROR(INDEX($B$2:$B$24848,MATCH($K6,IF($L6>=$C$2:$C$24848,IF($L6<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M7=SUMPRODUCT(--($L7>=$C$2:$C$24848),--($L7<=$D$2:$D$24848),--($K7=$A$2:$A$24848),$B$2:$B$24848)
N7{=IFERROR(INDEX($B$2:$B$24848,MATCH($K7,IF($L7>=$C$2:$C$24848,IF($L7<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M8=SUMPRODUCT(--($L8>=$C$2:$C$24848),--($L8<=$D$2:$D$24848),--($K8=$A$2:$A$24848),$B$2:$B$24848)
N8{=IFERROR(INDEX($B$2:$B$24848,MATCH($K8,IF($L8>=$C$2:$C$24848,IF($L8<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M9=SUMPRODUCT(--($L9>=$C$2:$C$24848),--($L9<=$D$2:$D$24848),--($K9=$A$2:$A$24848),$B$2:$B$24848)
N9{=IFERROR(INDEX($B$2:$B$24848,MATCH($K9,IF($L9>=$C$2:$C$24848,IF($L9<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M10=SUMPRODUCT(--($L10>=$C$2:$C$24848),--($L10<=$D$2:$D$24848),--($K10=$A$2:$A$24848),$B$2:$B$24848)
N10{=IFERROR(INDEX($B$2:$B$24848,MATCH($K10,IF($L10>=$C$2:$C$24848,IF($L10<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}
M11=SUMPRODUCT(--($L11>=$C$2:$C$24848),--($L11<=$D$2:$D$24848),--($K11=$A$2:$A$24848),$B$2:$B$24848)
N11{=IFERROR(INDEX($B$2:$B$24848,MATCH($K11,IF($L11>=$C$2:$C$24848,IF($L11<=$D$2:$D$24848,$A$2:$A$24848)),0)),"")}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Thanks for furnishing the file. I copied the formulae and they're still not quite working right. The Michigan-Michigan State issue has been resolved but now some rankings aren't showing up in the date range. I don't know if I have too cells for Excel to handle or what the problem is.

Is it possible to upload the file somewhere? Would somebody be willing to look at it?
 
Upvote 0
You could use one of the free file sharing sites like DropBox. You could also post a small sample of the issue by putting a boarder around the cells and then using copy paste.
In looking at your image in post#13 it looks like you have some mixed data in your cells A-D. The date in row 1880 column C & D don't look like dates??
 
Upvote 0
You could use one of the free file sharing sites like DropBox. You could also post a small sample of the issue by putting a boarder around the cells and then using copy paste.
In looking at your image in post#13 it looks like you have some mixed data in your cells A-D. The date in row 1880 column C & D don't look like dates??

Yep, you were right, some of the cells were not dates. When I changed the end dates of some of the cells, I must have pasted as values and somehow screwed up the format. I have fixed them now, but I am still having so many problems with the file. Neither formula is working.

Here's a Dropbox URL.

https://www.dropbox.com/s/t77dck77k27wcrb/Book2.xlsx?dl=0

Any ideas what's going wrong?
 
Upvote 0
Your file was too large for my computer to handle the array formulas on all the data. My suggestion would be to break your file down into a number of files maybe using only 5 years of data at a time. There maybe a VBA solution to the issue, but I don't know enough VBA to help you there. The other option would be to use a database program like Access or Open Office.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top