INDEX,MATCH with Greater Than Date Criteria

[Danigirl]

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?

 

[Danigirl]

Hi Danigirl,

Did you try my last suggestion?

Markmzz

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.

 

[markmzz]

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

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

 

[Danigirl]

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

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

 

[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

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

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