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