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?

 

[Redwolfx]

If your Batch #s are #s you could use

=SUMPRODUCT(('Batches Ran'!$A$1:$A$2000<='Blotters'!A1)*('Batches Ran'!$B$1:$B$2000='Blotters'!B1)*('Batches Ran'!$C$1:$C$2000='Blotters'!C1)*('Batches Ran'!$D$1:$D$2000))

 

[Danigirl]

If your Batch #s are #s you could use

=SUMPRODUCT(('Batches Ran'!$A$1:$A$2000<='Blotters'!A1)*('Batches Ran'!$B$1:$B$2000='Blotters'!B1)*('Batches Ran'!$C$1:$C$2000='Blotters'!C1)*('Batches Ran'!$D$1:$D$2000))

Thank you Redwolfx,

Unfortunately, the batch#'s are a combination of letters and numbers, so this formula did not work. It gives me a #VALUE error.

 

[spoon_db]

Let's clear:

The amount is the same, the sponsor is the same, the date of Bottler is always previous or equal with the date of entry... What have to be done when "Date of Entry is after the Date of Blotter?"...

Regards,

 

[AhoyNC]

It would help if you could post a small sample of your data, but maybe something like below.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Date Entry	Spon Code	Amount	Batch
2	6/3/2014	S2	24	A2
3
Blotters

Excel Workbook
	A	B	C	D
1	Date Entry	Spon Code	Amount	Batch
2	5/25/2014	S1	23	A1
3	6/4/2014	S2	24	A2
4	6/10/2014	S1	25	A3
Batches Ran

 

[Danigirl]

Let's clear:

The amount is the same, the sponsor is the same, the date of Bottler is always previous or equal with the date of entry... What have to be done when "Date of Entry is after the Date of Blotter?"...

Regards,

Yes, you are correct about the information.

Basically, we receive a batch and it is logged on a "Blotters" spreadsheet with the date of receipt (Date of Blotter), the amount, and the sponsor. However, it does not receive a Batch# until it is entered into the company's system, which could be the same day or shortly after.

Once the Batch is entered in the company's system, it receives a batch#. The information from that entry is logged into another spreadsheet ("Batches Ran") with the Date of Entry, the amount, the sponsor, and the Batch#.

The reason the date is so important is because the batches can come in with the same Amount and Sponsor information as before, with the only difference being the date. Therefore the batch#'s will be different.

Either way, whether the Date of Entry is the same day or after the Date of Blotter, we need to find the Batch number to connect to the Blotter spreadsheet.

Hopefully I made things more clear instead of confusing the process more.

 

[Danigirl]

AhoyNC,

Thanks for you response. I did try the formula you suggested, and it returned a batch# that had no matching information at all. The amount and sponsor were different than the matching criteria.I tried it a couple of times to make sure I had everything right, but it didn't give me the results I needed.

Here are quick samples of the spreadsheets. Hopefully these samples will help.

Blotters


-- removed inline image ---

The formula here needs to return Batch# MB52 since theentry date is after the Blotter date.

Batches Ran


-- removed inline image ---

 

[Danigirl]

Thank you for your suggestion. I tried the formula you recommended, however it returned a Batch# that was not associated with any of the matching criteria. I tried it a couple of times to make sure I inputted it correctly, but it did not give me the correct results.

Here are quick samples of the spreadsheets. Hopefully these samples will help.

Blotters

	A	B	C	D
1	Date of Blotter	Sponsor Code	Amount	Batch
2	6/2/2014	MAN	2.83	Formula

<tbody>
</tbody>

The formula here needs to return Batch# MB52 since the Entry date is after the Blotter date.

Batches Ran

	A	B	C	D
1	Date of Entry	Sponsor Code	Amount	Batch
2	5/6/2014	FRK	149.55	MF266
3	5/6/2014	MTL	18.15	MB43
4	5/18/2014	MAN	2.83	MB47
5	6/3/2014	GLT	4.35	XF2934
6	6/5/2014	MAN	2.83	MB52

<tbody>
</tbody>

 

[AhoyNC]

Try:
Make sure you change cell references to match your data.
Array formula that must be entered with CTRL-SHIFT-ENTER

Excel Workbook
	A	B	C	D
1	Date of Blotter	Sponsor Code	Amount	Batch
2	6/2/2014	MAN	2.83	MB52
Blotters

Excel Workbook
	A	B	C	D
1	Date of Entry	Sponsor Code	Amount	Batch
2	5/6/2014	FRK	149.55	MF266
3	5/6/2014	MTL	18.15	MB43
4	5/18/2014	MAN	2.83	MB47
5	6/3/2014	GLT	4.35	XF2934
6	6/5/2014	MAN	2.83	MB52
Batches Ran

 

[markmzz]

Maybe this:

Layout

Date of Blotter	Sponsor Code	Amount	Batch
18/05/2014	MAN	2.83	MB47	MB52
02/06/2014	MAN	2.83	MB52
*************	*************	********	*******	*******	*******

<colgroup><col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" span="3"> <tbody> </tbody>

Formula

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

=IFERROR(INDEX('Batches Ran'!$D$2:$D$6,SMALL(IF('Batches Ran'!$B$2:$B$6=$B2,IF('Batches Ran'!$C$2:$C$6=$C2,IF('Batches Ran'!$A$2:$A$6>=$A2,
ROW('Batches Ran'!$B$2:$B$6)-ROW('Batches Ran'!$B$2)+1))),COLUMNS($D2:D2))),"")

And copy to the right and down.

Markmzz