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

K

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.

SchoolGame date
Oklahoma12/1/1987
Virginia12/24/1987
Virginia1/10/1988
Oklahoma11/27/1988
Oklahoma11/30/1988
Oklahoma12/22/1988

<tbody>
</tbody>

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:

SchoolRankingRank startRank end
Duke311/6/198711/13/1987
Duke610/31/198811/6/1988
Oklahoma1511/20/1988
11/27/1988
North Carolina
81/15/19891/22/1989
Syracuse123/1/19883/8/1988

<tbody>
</tbody>

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:
Sort by date Sort by votes
kmprice710 said:
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.
Click to expand...
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
AhoyNC said:
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
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

<tbody>
</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
Click to expand...


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
AhoyNC said:
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
1SchoolRankingRank startRank end

SchoolDateRankINDEX
2Duke311/6/198711/13/1987

Oklahoma11/25/19880
3Duke610/31/198811/6/1988

Virginia12/24/19870
4Michigan1511/17/201411/23/2014

Virginia1/10/19880
5North Carolina81/15/19891/22/1989

Michigan State11/20/20141010
6Michigan State1011/17/201411/23/2014

Oklahoma11/30/19880
7Duke53/1/19883/8/1988

Oklahoma12/22/19880
8





Duke3/5/198855
9





Duke3/7/198855
10





North Carolina1/20/198988
11





Michigan11/20/20141515

<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>
</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
Click to expand...


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
AhoyNC said:
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??
Click to expand...

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
You must log in or register to reply here.

Similar threads

K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
172
XLfriend777
XLfriend777
I
  • Question
Formula to match criteria
2
Replies
18
Views
527
ItalianPlatinum
I
Cubist
  • Solved
Return values based on date range
Replies
3
Views
167
Peter_SSs
Peter_SSs
T
  • Question
cross country meet scoring
Replies
7
Views
141
Joe4
Joe4
D
  • Solved
Sectional Ranking, is it possible
Replies
3
Views
260
Joe4
Joe4

Forum statistics

Threads
1,215,343
Messages
6,124,394
Members
449,155
Latest member
ravioli44

Share this page

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