INDEX,MATCH with Greater Than Date Criteria

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:
 
I did try it but must be doing something wrong because all I get is the blank cell ("") which indicates there is an error somewhere.

Hi Danigirl,

Here all is ok. Look at this (with your example of the post #8):

Layout

[TABLE="width: 446"]
<tbody>[TR]
[TD="width: 67, bgcolor: transparent"]Date of Entry[/TD]
[TD="width: 71, bgcolor: transparent"]Sponsor Code[/TD]
[TD="width: 47, bgcolor: transparent"]Amount[/TD]
[TD="width: 42, bgcolor: transparent"]Batch[/TD]
[TD="width: 67, bgcolor: transparent"]Batches Ran[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"]Date of Blotter[/TD]
[TD="width: 72, bgcolor: transparent"]Sponsor Code[/TD]
[TD="width: 47, bgcolor: transparent"]Amount[/TD]
[TD="width: 42, bgcolor: transparent"]Batch[/TD]
[TD="width: 47, bgcolor: transparent"]Blotters[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]06/05/2014[/TD]
[TD="bgcolor: transparent"]FRK[/TD]
[TD="bgcolor: transparent"]149.55[/TD]
[TD="bgcolor: transparent"]MF266[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]02/06/2014[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: yellow"]MB52[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]06/05/2014[/TD]
[TD="bgcolor: transparent"]MTL[/TD]
[TD="bgcolor: transparent"]18.15[/TD]
[TD="bgcolor: transparent"]MB43[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]01/06/2014[/TD]
[TD="bgcolor: transparent"]GLT[/TD]
[TD="bgcolor: transparent"]4.35[/TD]
[TD="bgcolor: yellow"]XF2934[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]18/05/2014[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: transparent"]MB47[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]********[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]03/06/2014[/TD]
[TD="bgcolor: transparent"]GLT[/TD]
[TD="bgcolor: transparent"]4.35[/TD]
[TD="bgcolor: transparent"]XF2934[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]05/06/2014[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: transparent"]MB52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Formula

Code:
In D2 - [COLOR=#ff0000][B]use Ctrl+Shift+Enter to enter the formula[/B][/COLOR]

=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),"")

By the way, did you used Ctrl+Shift+Enter to enter the formula?

Markmzz
 
Upvote 0
Hi Danigirl,

Here all is ok. Look at this (with your example of the post #8):

Layout

[TABLE="width: 446"]
<tbody>[TR]
[TD="width: 67, bgcolor: transparent"]Date of Entry[/TD]
[TD="width: 71, bgcolor: transparent"]Sponsor Code[/TD]
[TD="width: 47, bgcolor: transparent"]Amount[/TD]
[TD="width: 42, bgcolor: transparent"]Batch[/TD]
[TD="width: 67, bgcolor: transparent"]Batches Ran[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"]Date of Blotter[/TD]
[TD="width: 72, bgcolor: transparent"]Sponsor Code[/TD]
[TD="width: 47, bgcolor: transparent"]Amount[/TD]
[TD="width: 42, bgcolor: transparent"]Batch[/TD]
[TD="width: 47, bgcolor: transparent"]Blotters[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]06/05/2014[/TD]
[TD="bgcolor: transparent"]FRK[/TD]
[TD="bgcolor: transparent"]149.55[/TD]
[TD="bgcolor: transparent"]MF266[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]02/06/2014[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: yellow"]MB52[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]06/05/2014[/TD]
[TD="bgcolor: transparent"]MTL[/TD]
[TD="bgcolor: transparent"]18.15[/TD]
[TD="bgcolor: transparent"]MB43[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]01/06/2014[/TD]
[TD="bgcolor: transparent"]GLT[/TD]
[TD="bgcolor: transparent"]4.35[/TD]
[TD="bgcolor: yellow"]XF2934[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]18/05/2014[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: transparent"]MB47[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]********[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]03/06/2014[/TD]
[TD="bgcolor: transparent"]GLT[/TD]
[TD="bgcolor: transparent"]4.35[/TD]
[TD="bgcolor: transparent"]XF2934[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]05/06/2014[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: transparent"]MB52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Formula

Code:
In D2 - [COLOR=#ff0000][B]use Ctrl+Shift+Enter to enter the formula[/B][/COLOR]

=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),"")

By the way, did you used Ctrl+Shift+Enter to enter the formula?

Markmzz

Mark,

Yes, I did used the Ctrl+Shift+Enter to create the array, but I forgot put in the '=$B2' in the inner most nested IF, so there was no logic test in that IF statement. I just corrected it now. However, can you please let me know why you use 'Batches Ran'!$C$2:$D$6 for the true criteria result in the inner most nested IF?

The reason for the question is because my actual spreadsheets are laid out differently; In my Batches Ran spreadsheet, the columns are actually A,B,C,D - 'Amount', 'Sponsor Code', 'Batches', 'Date of Entry' (respectively). I only used the information needed and made both spreadsheets the same in the examples to simplify things, but I've been adjusting the formulas to match the columns in the actual spreadsheets.

From your formula, I changed the columns in my spreadsheet for the true criteria result, which spans across three columns instead of just the two 'Batches Ran'!$A$2:$C$6. The result I'm getting now is the sponsor code instead of the Batch#.

Dani
 
Upvote 0
Mark,

Yes, I did used the Ctrl+Shift+Enter to create the array, but I forgot put in the '=$B2' in the inner most nested IF, so there was no logic test in that IF statement. I just corrected it now. However, can you please let me know why you use 'Batches Ran'!$C$2:$D$6 for the true criteria result in the inner most nested IF?

The reason for the question is because my actual spreadsheets are laid out differently; In my Batches Ran spreadsheet, the columns are actually A,B,C,D - 'Amount', 'Sponsor Code', 'Batches', 'Date of Entry' (respectively). I only used the information needed and made both spreadsheets the same in the examples to simplify things, but I've been adjusting the formulas to match the columns in the actual spreadsheets.

From your formula, I changed the columns in my spreadsheet for the true criteria result, which spans across three columns instead of just the two 'Batches Ran'!$A$2:$C$6. The result I'm getting now is the sponsor code instead of the Batch#.

Dani

Hi Dani,

In this case try this:

Layout

[TABLE="width: 449"]
<tbody>[TR]
[TD="width: 47, bgcolor: transparent"]Amount[/TD]
[TD="width: 71, bgcolor: transparent"]Sponsor Code[/TD]
[TD="width: 47, bgcolor: transparent"]Batch[/TD]
[TD="width: 67, bgcolor: transparent"]Date of Entry[/TD]
[TD="width: 67, bgcolor: transparent"]Batches Ran[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"]Date of Blotter[/TD]
[TD="width: 72, bgcolor: transparent"]Sponsor Code[/TD]
[TD="width: 47, bgcolor: transparent"]Amount[/TD]
[TD="width: 42, bgcolor: transparent"]Batch[/TD]
[TD="width: 47, bgcolor: transparent"]Blotters[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]149.55[/TD]
[TD="bgcolor: transparent"]FRK[/TD]
[TD="bgcolor: transparent"]MF266[/TD]
[TD="bgcolor: transparent, align: right"]06/05/2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]02/06/2014[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: yellow"]MB52[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18.15[/TD]
[TD="bgcolor: transparent"]MTL[/TD]
[TD="bgcolor: transparent"]MB43[/TD]
[TD="bgcolor: transparent, align: right"]06/05/2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]01/06/2014[/TD]
[TD="bgcolor: transparent"]GLT[/TD]
[TD="bgcolor: transparent"]4.35[/TD]
[TD="bgcolor: yellow"]XF2934[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]MB47[/TD]
[TD="bgcolor: transparent, align: right"]18/05/2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]********[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4.35[/TD]
[TD="bgcolor: transparent"]GLT[/TD]
[TD="bgcolor: transparent"]XF2934[/TD]
[TD="bgcolor: transparent, align: right"]03/06/2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2.83[/TD]
[TD="bgcolor: transparent"]MAN[/TD]
[TD="bgcolor: transparent"]MB52[/TD]
[TD="bgcolor: transparent, align: right"]05/06/2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Formula

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

=IFERROR(VLOOKUP($C2,IF('Batches Ran'!$D$2:$D$6>=$A2,IF('Batches Ran'!$B$2:$B$6=$B2,'Batches Ran'!$A$2:$C$6)),[COLOR=#ff0000][B]3[/B][/COLOR],0),"")

Or

=IFERROR(VLOOKUP($C2,IF('Batches Ran'!$D$2:$D$6>=$A2,IF('Batches Ran'!$B$2:$B$6=$B2,'Batches Ran'!$A$2:$C$6)),[B][COLOR="#FF0000"]MATCH(D$1,'Batches Ran'!$A$1:$C$1,0)[/COLOR][/B],0),"")

Markmzz
 
Upvote 0

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