Index Match To Return Date between two dates

J

JamesPa

New Member
Joined
Dec 23, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
I am trying to write an Index Match formula which will return the date of a service into the cells in column C in Worksheet 2. The data with the dates is in the worksheet titled "Paste Here". I have a list of clients in column B of Sheet 2. The same clients have a list of several services in "Paste Here". I know an Index Match formula will return the date of the January service for each client in the cells in column C, but I can't quite get it. I've tried three formulas, but can't get it right. The formula in cells C4 to C9 returns the date of the first service listed in Column G in "Paste Here". The formula in C3 is the closest to what I need, but I need some assistance. Can anyone help with this formula??






Transitons_UA_Tracker_111422.xlsx
ABCOPQRS
1MR#Patient NameJanuary-221/1/221/31/22
21A, I 2/1/222/28/22
32A, LJanuary 0, 19003/1/223/31/22
43A, S9/6/20224/1/224/30/22
54A, R5/13/20225/1/225/31/22
65A, St8/19/20226/1/226/30/22
76A, D8/26/20227/1/227/31/22
87A, Str9/7/20228/1/228/31/22
98A, C9/20/20229/1/229/30/22
109A, J10/1/2210/31/22
1110B, S11/1/2211/30/22
1212/1/2212/31/22
13
14
Sheet2
Cell Formulas
RangeFormula
C2C2=IFERROR(INDEX('Paste Here'!$G$2:$G$2169,AGGREGATE(15,6*(ROW('Paste Here'!$B$2:$B$2169)-ROW($B$2)+1)/(('Paste Here'!$B$2:$B$2169=Sheet2!B2)*('Paste Here'!$G$2:$G$2169<=S1)*('Paste Here'!$G$2:$G$2169>=R1)),1)),"")
C3C3=INDEX('Paste Here'!$G$2:$G$2169,MATCH(1,INDEX(('Paste Here'!$B$2:$B$2169=Sheet2!B3)*('Paste Here'!$G$2:$G$2169<=Sheet2!S1)*('Paste Here'!$G$2:$G$2169>=Sheet2!R1),0)*0))
C4:C9C4=INDEX('Paste Here'!$G$2:$G$2169,MATCH(Sheet2!B4,'Paste Here'!$B$2:$B$2189,0))


Transitons_UA_Tracker_111422.xlsx
ABCDEFG
1#NameDate
21A, I9/6/2022
31A, I3/22/2022
41A, I4/19/2022
51A, I10/13/2022
61A, I5/17/2022
71A, I5/17/2022
81A, I6/1/2022
91A, I1/25/2022
101A, I8/9/2022
112A, L1/6/2022
122A, L4/20/2022
132A, L9/21/2022
142A, L10/12/2022
152A, L5/11/2022
162A, L6/1/2022
172A, L7/21/2022
182A, L8/10/2022
192A, L2/16/2022
203A, S9/6/2022
213A, S3/21/2022
223A, S1/18/2022
233A, S10/17/2022
243A, S1/18/2022
253A, S2/7/2022
263A, S6/13/2022
273A, S7/25/2022
283A, S8/15/2022
294A, R5/13/2022
304A, R6/1/2022
314A, R7/6/2022
325A, St8/19/2022
335A, St8/29/2022
345A, St9/14/2022
355A, St3/21/2022
365A, St1/4/2022
375A, St4/8/2022
385A, St10/18/2022
395A, St4/20/2022
405A, St5/4/2022
415A, St5/23/2022
425A, St6/15/2022
435A, St7/19/2022
445A, St8/19/2022
455A, St2/18/2022
466A, D8/26/2022
476A, D9/13/2022
486A, D1/14/2022
496A, D4/8/2022
506A, D5/6/2022
516A, D6/3/2022
526A, D7/15/2022
536A, D2/11/2022
546A, Str9/7/2022
557A, Str4/5/2022
567A, Str5/5/2022
577A, Str6/2/2022
587A, Str7/22/2022
597A, Str8/10/2022
607A, Str6/30/2022
618A, C9/20/2022
628A, C10/20/2022
638A, C3/25/2022
648A, C4/5/2022
658A, C5/12/2022
668A, C2/7/2022
678A, C6/21/2022
688A, C7/8/2022
698A, C8/8/2022
709A, J9/7/2022
719A, J10/20/2022
729A, J8/1/2022
7310B, S8/18/2022
7410B, S9/1/2022
7510B, S9/15/2022
7610B, S3/16/2022
7710B, S1/10/2022
7810B, S10/27/2022
7910B, S4/28/2022
8010B, S5/26/2022
8110B, S2/4/2022
8210B, S6/16/2022
8310B, S7/14/2022
Paste Here
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
In C2 see if this gives you what you need.
Excel Formula: 
=LOOKUP(2,1/(('Paste Here'!$B$2:$B$5000=B2)*('Paste Here'!$G$2:$G$5000>=Sheet2!R2)*('Paste Here'!$G$2:$G$5000<=Sheet2!S2)),'Paste Here'!$G$2:$G$5000)
 
Upvote 0
Can you show me the a screenshot including row/column references and the formula bar with C2 selected to show me the formula in place.
Also show me the line on your "paste here" sheet that it should have picked up.
 
Upvote 0
Here you go. Row 9 in "Paste Here" is where the date is that should be pulled over. Cell G9 in Paste here.



Transitons_UA_Tracker_111422.xlsx
ABCOPQRS
1MR#Patient NameJanuary-221/1/221/31/22
21A, I#N/A2/1/222/28/22
32A, LJanuary 0, 19003/1/223/31/22
43A, S9/6/20224/1/224/30/22
54A, R5/13/20225/1/225/31/22
65A, St8/19/20226/1/226/30/22
76A, D8/26/20227/1/227/31/22
87A, Str9/7/20228/1/228/31/22
98A, C9/20/20229/1/229/30/22
109A, J10/1/2210/31/22
1110B, S11/1/2211/30/22
1212/1/2212/31/22
Sheet2
Cell Formulas
RangeFormula
C2C2=LOOKUP(2,1/(('Paste Here'!$B$2:$B$5000=B2)*('Paste Here'!$G$2:$G$5000>=Sheet2!R2)*('Paste Here'!$G$2:$G$5000<=Sheet2!S2)),'Paste Here'!$G$2:$G$5000)
C3C3=INDEX('Paste Here'!$G$2:$G$2169,MATCH(1,INDEX(('Paste Here'!$B$2:$B$2169=Sheet2!B3)*('Paste Here'!$G$2:$G$2169<=Sheet2!S1)*('Paste Here'!$G$2:$G$2169>=Sheet2!R1),0)*0))
C4:C9C4=INDEX('Paste Here'!$G$2:$G$2169,MATCH(Sheet2!B4,'Paste Here'!$B$2:$B$2189,0))


Transitons_UA_Tracker_111422.xlsx
ABCDEFG
1#NameDate
21A, I9/6/2022
31A, I3/22/2022
41A, I4/19/2022
51A, I10/13/2022
61A, I5/17/2022
71A, I5/17/2022
81A, I6/1/2022
91A, I1/25/2022
101A, I8/9/2022
112A, L1/6/2022
122A, L4/20/2022
132A, L9/21/2022
142A, L10/12/2022
152A, L5/11/2022
162A, L6/1/2022
172A, L7/21/2022
182A, L8/10/2022
192A, L2/16/2022
203A, S9/6/2022
213A, S3/21/2022
223A, S1/18/2022
233A, S10/17/2022
243A, S1/18/2022
253A, S2/7/2022
263A, S6/13/2022
273A, S7/25/2022
283A, S8/15/2022
294A, R5/13/2022
304A, R6/1/2022
314A, R7/6/2022
325A, St8/19/2022
335A, St8/29/2022
345A, St9/14/2022
355A, St3/21/2022
365A, St1/4/2022
375A, St4/8/2022
385A, St10/18/2022
395A, St4/20/2022
405A, St5/4/2022
415A, St5/23/2022
425A, St6/15/2022
435A, St7/19/2022
445A, St8/19/2022
455A, St2/18/2022
466A, D8/26/2022
476A, D9/13/2022
486A, D1/14/2022
496A, D4/8/2022
506A, D5/6/2022
516A, D6/3/2022
526A, D7/15/2022
536A, D2/11/2022
546A, Str9/7/2022
557A, Str4/5/2022
567A, Str5/5/2022
577A, Str6/2/2022
587A, Str7/22/2022
597A, Str8/10/2022
607A, Str6/30/2022
618A, C9/20/2022
628A, C10/20/2022
638A, C3/25/2022
648A, C4/5/2022
658A, C5/12/2022
668A, C2/7/2022
678A, C6/21/2022
688A, C7/8/2022
698A, C8/8/2022
709A, J9/7/2022
719A, J10/20/2022
729A, J8/1/2022
7310B, S8/18/2022
7410B, S9/1/2022
7510B, S9/15/2022
7610B, S3/16/2022
7710B, S1/10/2022
7810B, S10/27/2022
7910B, S4/28/2022
8010B, S5/26/2022
8110B, S2/4/2022
8210B, S6/16/2022
8310B, S7/14/2022
Paste Here
 
Upvote 0
I am confused.
Row 2 in sheet has a date range of Feb 2022. Row 9 in Paste Here is in Jan 2022 and as such does not meet the criteria and #N/A would seem to me to be correct (you can cover this off with an Iferror() )
 
Upvote 0
sorry for causing confusion. I entered the date ranges in columns R and S in Sheet 2 to reference in the forumla. The date ranges begin on Row 1, January 1, 2022 is R1 and January 31, 2022 is S1.
I entered the formula with the correct cells for the January dates. I am still getting the #N/A. Formula below.

=LOOKUP(2,1/(('Paste Here'!$A$2:$A$5000=A2)*('Paste Here'!$G$2:$G$5000>=Sheet2!R1)*('Paste Here'!$G$2:$G$5000<=Sheet2!S1)),'Paste Here'!$G$2:$G$5000)
 
Upvote 0
I am a bit confused.
If you are using a common date range of Jan per R1 and S1 for all rows then you would need to absolute value the address.
We also seem to have changed to using MR# for the lookup instead of name.
So the following should work.
Excel Formula: 
=LOOKUP(2,1/(
                           ('Paste Here'!$A$2:$A$5000=A2)*
                           ('Paste Here'!$G$2:$G$5000>=$R$1)*
                          ('Paste Here'!$G$2:$G$5000<=$S$1)),
                 'Paste Here'!$G$2:$G$5000)
If it doesn't is MR# in both sheets being recognised as a number and are the dates all being recognised as dates.
eg if you change the number or date format on all the cells used does the way it displays change (if not it is text).

If the above doesn't work can you put your spreadsheet on google drive, drop box or some other sharing site making it available to anyone with the link and posting the link here ?
 
Upvote 0
Solution
Each Patient has a unique MR#, so I can use the MR# or name in the formula. The MR# and patient name are listed on "Paste Here".
Here is a link to the file on my Google Drive. Please let me know if you have any issues with it.


Thank you very much for your assistance with this.
 
Upvote 0
Your column G in Paste Here are all text values.
If this is a one off, you can use Text to Columns to convert it.
If this is a regular process, can you add a column H that just does =Value(G2) and use H in your Lookup ?
What is going to work best for you when you need to refresh the data ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
392
Asbestos_Jen
Asbestos_Jen
Z
  • Question Question
Breaking down some dates using a formula
2
Replies
13
Views
554
zelarra
Z
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
319
etaf
etaf

Forum statistics

Threads
1,224,828
Messages
6,181,213
Members
453,024
Latest member
Wingit77

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