INDEX,MATCH with Greater Than Date Criteria

D

Danigirl

New Member
Joined
Jun 19, 2014
Messages
9
Can someone please help me figure out a formula to index a specific batch# from an array which is based on matching an amount, a sponsor code, and a date that may be greater than or equal to the specified date?

Example (2 worksheets in the workbook):

Worksheet 1 - "Batches Ran"
Column A - Date of entry
Column B - Sponsor code
Column C - Amount
Column D - Batch#

Worksheet 2 - "Blotters"
Column A - Date of Blotter
Column B - Sponsor
Column C - Amount
Column D - Formula to match above info to info in Batches_Ran worksheet and result in the Batch#

{=INDEX('Batches Ran'!$D$1:$D$2000,MATCH('Blotters'!A1&'Blotters'!B1&'Blotters'!C1,'Batches Ran'!$A$1:$A$2000&'Batches Ran'!$B$1:$B$2000&'Batches Ran'!$C$1:$C$2000,0))}

The problem is that the Date of Blotter is always going to be previous to (less than) or equal to the Date of Entry. This formula works if the date is the same, but it returns an #N/A error if the Date of Entry is after the Date of Blotter.

I tried using the -1 in the match_type field, but that only returned the header value in some of the cells, not the correct batch number. I also tried using the 1 in the match_type field, and that produced more incorrect results.

I tried using an IF statement before the Index [=IF('Blotters'!A1<='Batches Ran'!$A$1:$A$2000,INDEX...], but that only worked for the earliest date, all other dates returned a false value.

Can this be done with a formula, or am I going to need to use VBA for this? :confused:
 
Sort by date Sort by votes
Using Ahoys Formula

Try

=INDEX('Batches Ran'!$D$1:$D$2000,MATCH(Blotters!B1,IF(Blotters!C1='Batches Ran'!$C$1:$C$2000,IF('Batches Ran'!$A$1:$A$2000>=Blotters!A1,'Batches Ran'!$B$1:$B$2000)),0))

Confirm with Ctrl+Shift+Enter of course
 
Upvote 0
Rich (BB code): 
Blotters

[TABLE="width: 400"]
<tbody>[TR]
[TD]Date of Blotter[/TD]
[TD]Sponsor Code[/TD]
[TD]Amount[/TD]
[TD]Batch[/TD]
[/TR]
[TR]
[TD]6/2/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/2/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Batches Ran

[TABLE="width: 400"]
<tbody>[TR]
[TD="class: xl65, width: 100"]Date of   Entry[/TD]
[TD="class: xl65, width: 100"]Sponsor Code[/TD]
[TD="class: xl65, width: 100"]Amount[/TD]
[TD="class: xl65, width: 100"]Batch[/TD]
[/TR]
</tbody>[/TABLE]
 [TABLE="width: 400"]
<tbody>[TR]
[TD]6/5/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD]MB52[/TD]
[/TR]
[TR]
[TD]6/5/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD]MB53[/TD]
[/TR]
</tbody>[/TABLE]

Is this a possible scenario?
 
Upvote 0
You could also try:

CTRL+SHIFT+ENTER not required

Code: 
=INDEX('Batches Ran'!$D$2:$D$6,AGGREGATE(14,6,(--('Batches Ran'!$A$2:$A$6>=Blotters!$A$2))*
(--MONTH('Batches Ran'!$A$2:$A$6)=MONTH(Blotters!$A$2))*
(--('Batches Ran'!$B$2:$B$6=Blotters!$B$2))*(MATCH('Batches Ran'!$D$2:$D$6,'Batches Ran'!$D$2:$D$6,0)),1))

Not sure if all the double negatives are required or how fast it would calculate in a massive data set

You could also replace the
Code: 
(MATCH('Batches Ran'!$D$2:$D$6,'Batches Ran'!$D$2:$D$6,0))

with the
Code: 
=ROW($a$2:$a$17)-ROW($a$2)+1
construct for relative positions

hope this helps
 
Last edited:
Upvote 0
Another way:

Code: 
In D2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(VLOOKUP($C2,IF('Batches Ran'!$A$2:$A$6>=$A2,IF('Batches Ran'!$B$2:$B$6=$B2,'Batches Ran'!$C$2:$D$6)),2,0),"")

Markmzz
 
Upvote 0
Redwolfx said:
Using Ahoys Formula

Try

=INDEX('Batches Ran'!$D$1:$D$2000,MATCH(Blotters!B1,IF(Blotters!C1='Batches Ran'!$C$1:$C$2000,IF('Batches Ran'!$A$1:$A$2000>=Blotters!A1,'Batches Ran'!$B$1:$B$2000)),0))

Confirm with Ctrl+Shift+Enter of course
Click to expand...

Thanks Red and AhoyNC, This works! :biggrin:

Thanks also to everyone! I really appreciate everyone's help. You are all amazing, and I enjoy this forum very much.
 
Upvote 0
spoon_db said:
Rich (BB code): 
Blotters

[TABLE="width: 400"]
<tbody>[TR]
[TD]Date of Blotter[/TD]
[TD]Sponsor Code[/TD]
[TD]Amount[/TD]
[TD]Batch[/TD]
[/TR]
[TR]
[TD]6/2/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/2/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Batches Ran

[TABLE="width: 400"]
<tbody>[TR]
[TD="class: xl65, width: 100"]Date of   Entry[/TD]
[TD="class: xl65, width: 100"]Sponsor Code[/TD]
[TD="class: xl65, width: 100"]Amount[/TD]
[TD="class: xl65, width: 100"]Batch[/TD]
[/TR]
</tbody>[/TABLE]
 [TABLE="width: 400"]
<tbody>[TR]
[TD]6/5/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD]MB52[/TD]
[/TR]
[TR]
[TD]6/5/2014[/TD]
[TD]MAN[/TD]
[TD]2.83[/TD]
[TD]MB53[/TD]
[/TR]
</tbody>[/TABLE]

Is this a possible scenario?
Click to expand...

I didn't think it was until I went through the data, and although very rare, the scenario is possible on occasion.

I used Red and Ahoy's formula, which worked. Is there something we can use in that formula to distinguish between the repetitive info in all three fields? If not, it's not a huge deal, but if we can, I'd like to have it all automated. Would I do an IF(the batch is already listed in the Blotters sheet, use the next one with the same info) kind of thing?
 
Upvote 0
markmzz said:
Another way:

Code: 
In D2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(VLOOKUP($C2,IF('Batches Ran'!$A$2:$A$6>=$A2,IF('Batches Ran'!$B$2:$B$6=$B2,'Batches Ran'!$C$2:$D$6)),2,0),"")

Markmzz
Click to expand...
Hi Danigirl,

Did you try my last suggestion?

Markmzz
 
Upvote 0
short story... try
Rich (BB code): 
=INDEX('Batches Ran'!$D$2:$D$30;SMALL(IF(('Batches Ran'!$A$2:$A$30>=Blotters!A2)*('Batches Ran'!$B$2:$B$30=Blotters!B2)*('Batches Ran'!$C$2:$C$30=Blotters!C2);ROW('Batches Ran'!$D$2:$D$30)-ROW('Batches Ran'!$D$2)+1);1))

code is to row 30, expand it...
RED digit at the end of the formula define which match to be displayed - first, second, third...

i suggest you to set a conditional formatting to paint the cell if there is a repeat in Data & Sponsor & Amount and you manually to change which Batch you need... change the digit to 2... that if you have repeat in orders very rear...

If they're more, 2-3 times a month, you should use a second column in which you have CountIF on Data & Sponsor & Amount and this will be the consecutive number of the position - red number in the formula...

Regards,
 
Upvote 0
Danigirl,

Markmzz's formula will work if you have duplicates.
So, in the example below you would enter in cell D2 and copy across to E2 or more columns if there could be more than 2 Batch #'s.

Excel Workbook
ABCDEF
1Date of BlotterSponsor CodeAmountBatch
26/2/2014MAN2.83MB47MB52
3
Blotters



Excel Workbook
ABCD
1Date of EntrySponsor CodeAmountBatch
25/6/2014FRK149.55MF266
35/6/2014MTL18.15MB43
46/2/2014MAN2.83MB47
56/3/2014GLT4.35XF2934
66/2/2014MAN2.83MB52
Batches Ran
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Locked
  • Question Question
Help with Index & Match
Replies
1
Views
199
Fluff
Fluff
S
  • Question Question
populating rows based on dates
Replies
1
Views
116
zero269
Z
Sumeluar
  • Question Question
Match a task if it is within a date range
Replies
0
Views
163
Sumeluar
Sumeluar
P
  • Solved
Dynamic Sheet Tabs in Index Match function
Replies
9
Views
593
Peter_SSs
Peter_SSs
A
  • Solved
Index Match Function
Replies
3
Views
339
Fluff
Fluff

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