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:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
You could use INDEX / MATCH formula. Cell C2 below
This is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag down as needed.

Or
You could use SUMPRODUCT, just copy down as needed. Cell D2 below.
Excel Workbook
ABCD
1SchoolGame dateRankRank
2Oklahoma12/1/1987 0
3Virginia12/24/19870
4Virginia1/10/19880
5Oklahoma11/27/19881515
6Oklahoma11/30/19880
7Duke11/4/198866
8
9
10SchoolRankingRank startRank end
11Duke311/6/198711/13/1987
12Duke610/31/198811/6/1988
13Oklahoma1511/20/198811/27/1988
14North Carolina81/15/19891/22/1989
15Syracuse123/1/19883/8/1988
Sheet
 
Upvote 0
AhoyNC said:
You could use INDEX / MATCH formula. Cell C2 below
This is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag down as needed.

Or
You could use SUMPRODUCT, just copy down as needed. Cell D2 below.


ABCD
1SchoolGame dateRankRank
2Oklahoma12/1/1987
0
3Virginia12/24/1987
0
4Virginia1/10/1988
0
5Oklahoma11/27/19881515
6Oklahoma11/30/1988
0
7Duke11/4/198866
8



9



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

<colgroup><col style="width:30px; "><col style="width:114px;"><col style="width:104px;"><col style="width:83px;"><col style="width:83px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C2{=IFERROR(INDEX($B$11:$B$15,MATCH(A2,IF($B2>=$C$11:$C$15,IF($B2<=$D$11:$D$15,$A$11:$A$15)),0)),"")}
D2=SUMPRODUCT(--($B2>=$C$11:$C$15),--($B2<=$D$11:$D$15),--($A2=$A$11:$A$15),$B$11:$B$15)

<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, thanks for responding. I tried both methods and neither one worked. I got all blanks with the first method and all 0s with the second method. I'm starting to think there's some problem with the format of the date cells, but I can't figure it out.
 
Upvote 0
Make sure that the dates in both tables are actual Excel dates and not text.
You can use the ISNUMBER function to check (example below).
Excel Workbook
AB
11/2/1988TRUE
24/5/98FALSE
Sheet
 
Upvote 0
AhoyNC said:
Make sure that the dates in both tables are actual Excel dates and not text.
You can use the ISNUMBER function to check (example below).


AB
11/2/1988TRUE
24/5/98FALSE

<colgroup><col style="width:30px; "><col style="width:90px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=ISNUMBER(A1)
B2=ISNUMBER(A2)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Yes, all of them checked out, so that was fine. I found one part of the SUMPRODUCT was pointed at the wrong cell in my version, so I fixed that. But... although I'm doing it "more right" than before, it's still wrong. Now I'm getting numbers like 50 that don't appear as a ranking. Rankings go 1-25. I'm getting 50, 29, 31, and other numbers >25. That doesn't seem possible if I don't have any numbers that high in that column. Any wild guess what's wrong?
 
Upvote 0
AhoyNC said:
You could use INDEX / MATCH formula. Cell C2 below
This is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag down as needed.

Or
You could use SUMPRODUCT, just copy down as needed. Cell D2 below.


ABCD
1SchoolGame dateRankRank
2Oklahoma12/1/1987
0
3Virginia12/24/1987
0
4Virginia1/10/1988
0
5Oklahoma11/27/19881515
6Oklahoma11/30/1988
0
7Duke11/4/198866
8



9



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

<colgroup><col style="width:30px; "><col style="width:114px;"><col style="width:104px;"><col style="width:83px;"><col style="width:83px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C2{=IFERROR(INDEX($B$11:$B$15,MATCH(A2,IF($B2>=$C$11:$C$15,IF($B2<=$D$11:$D$15,$A$11:$A$15)),0)),"")}
D2=SUMPRODUCT(--($B2>=$C$11:$C$15),--($B2<=$D$11:$D$15),--($A2=$A$11:$A$15),$B$11:$B$15)

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

Come to think of it, I can't even duplicate the examples. I get all blanks in column C and all zeroes in column D.
 
Upvote 0
kmprice710 said:
Come to think of it, I can't even duplicate the examples. I get all blanks in column C and all zeroes in column D.
Click to expand...

Ok nevermind, I got the example to work by clicking on the cells. Still can't get my real life one to work. Still getting 45, 50, 35, numbers that don't appear in the ranking column.
 
Upvote 0
Post a copy of the actual formula you are using.
What columns hold your actual data and what row do they start in?
Are your tables in different sheets?
Are the INDEX formula (make sure you enter it with CTRL-SHIFT-ENTER) and SUMPRODUCT formula giving the same answer?

Can you post a small sample of your data that is producing the error?
 
Last edited:
Upvote 0
AhoyNC said:
Post a copy of the actual formula you are using.
What columns hold your actual data and what row do they start in?
Are your tables in different sheets?
Are the INDEX formula (make sure you enter it with CTRL-SHIFT-ENTER) and SUMPRODUCT formula giving the same answer?

Can you post a small sample of your data that is producing the error?
Click to expand...

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.
 
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
156
XLfriend777
XLfriend777
I
  • Question
Formula to match criteria
2
Replies
18
Views
511
ItalianPlatinum
I
Cubist
  • Solved
Return values based on date range
Replies
3
Views
154
Peter_SSs
Peter_SSs
T
  • Question
cross country meet scoring
Replies
7
Views
118
Joe4
Joe4
D
  • Solved
Sectional Ranking, is it possible
Replies
3
Views
258
Joe4
Joe4

Forum statistics

Threads
1,214,952
Messages
6,122,457
Members
449,083
Latest member
Ava19

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