Index/Match with 2 criteria - cannot get it to work

[jgold20]

I have 3 sheets:
1. Sheet 1 is for data entry
2. Sheet 2 is for the results
3. Sheet 3 is a work sheet

All columns in sheet 3 are correct. Columns J and K are sorted in the correct sequence. When the results are in place on sheet 2, the dates are in the correct sequence, but the time is not. This occurs only when the same date and time are entered into sheet 1 more than once but on the second occurrence the time is < the first one entered for the same date.

Sheet 1

Follow-Up	Notes	MM/DD/YY	Time
Test 1	AAAA	2/10/19	7:00 PM
Test 2	BBBB	2/10/19	5:00 PM

<tbody>
</tbody>

Sheet 2

Date	Follow-Up	Notes	Time
2/10/19	Test 1	AAAA	7:00 PM
2/10/19	Test 2	BBBB	5:00 PM

<tbody>
</tbody>

Sheet 3
Col f Col G Col H Col I Col J Col K Col L

Event Name	Event Date (2019)	Time	Notes	Sorted 2019 Date	Sorted 2019 Time	Row number Date
Test 1	2/10/19	7:00 PM	AAAA	2/10/19	5:00 PM	1
Test 2	2/10/19	5:00 PM	BBBB	2/10/19	7:00 PM	2

<tbody>
</tbody>

Define name - Input_Events= =data!$F$3:$I$102

Column F = Event
Column G = Date
Column H = Time
Column I = Notes
Column J = sorted date (using small)
Column k = sorted date (using small)
Column L = Formula-=IFERROR(IF(COUNTIF($J$3:J3,J3)=1,MATCH(J3,INDEX(Input_Events,,2),0),MATCH(J3,INDEX(INDIRECT(ADDRESS(ROW(Input_Events)+L2,6,1,1)):data!$G$102,,2),0)+L2),"")

Any help would be greatly appreciated.

 

[Chrisdontm]

Ok, lets go this route, and remove all array formulas;

Using your original setup, on Sheet3;
Remove column: K and, replace column: J with this formula;

=G3+H3 You can call this column: Combo Time
( It combines the Date and the Time )

Create a new column, column: E
Put this formula in cell: E3
=IFERROR(IF(J3=0,"",COUNTIFS($J$3:$J$102,"<>"&0,$J$3:$J$102,"<"&J3)+1),"")

Then over on your RESULTS sheet put this formula in cell: F3
=IFERROR(VLOOKUP(ROWS($A$1:A1),Sheet3!$E$3:$J$102,3,0),"")

In: G3
=IFERROR(VLOOKUP(ROWS($A$1:B1),Sheet3!$E$3:$J$102,2,0),"")

In: H3
=IFERROR(VLOOKUP(ROWS($A$1:C1),Sheet3!$E$3:$J$102,5,0),"")

In: I3
=IFERROR(VLOOKUP(ROWS($A$1:D1),Sheet3!$E$3:$J$102,4,0),"")

This ought to take care of you...

 

[jgold20]

Just to confirm:

1) Column J on sheet3 (data) I have defined as MM/DD/YYYY,HH/MM/SS

The new formulas are now working. However, the way the "results" sheet is laid out, I span over 2 different areas for a total of 8 columns, Z6:AC55 and AE6 - AE55 so you do not have 1 long area that you have to scroll down.

Z6:AC55 is working perfectly. AE6 - AE55 is not working. Any suggestions.

Thanks again for all of your help

 

[Chrisdontm]

Which formula, or answers are being used in column " AE " on the results page...?

I assume that columns Z:AC are the headings;

Date

Follow-Up

Notes

Time

<colgroup><col width="82" span="4"></colgroup><tbody>
</tbody>

 

[jgold20]

The headings are in columns Z6 - AI6.

To better explain what is happening, columns AD7 - AD55 and AI7 - AI55 is looking for the date to display a message past due, today or tomorrow using a formula and conditional formatting. This works fine.

AI7 - AI55 is now duplicating/replicating the results from AD7 - AI55 (this is displaying the correct results).

Formulas - AE7 - AE55:
=IFERROR(VLOOKUP(ROWS($A$1:A1),data!$E$3:$J$102,3,0),"")

Formulas - AF7 - AF55:
=IFERROR(VLOOKUP(ROWS($A$1:A1),data!$E$3:$J$102,2,0),"")

Formulas - AG7 - AG55:
=IFERROR(VLOOKUP(ROWS($A$1:A1),data!$E$3:$J$102,5,0),"")

Formulas - AH7 - AH55
=IFERROR(VLOOKUP(ROWS($A$1:A1),data!$E$3:$J$102,6,0),"")

AD7 - AD55 and AI7 - AI55
If statement to test past due, today or tomorrow - WORKING

 

[Chrisdontm]

So everything looks like its working if you put the column "E" that I mentioned to put on Sheet3 onto the data sheet,

and the column "J" I mentioned to put on Sheet3 onto the data sheet as well. ( Mentioned in post # 11 )

 

[jgold20]

NO it is not working for all of the columns. Column E has been put in, however, it is only working on the first half of the entries (AD7 - AD55). The second half (AI7 - AI55) are exactly the same as the first half (AD7 - AD55).

Date	Follow-Up	Notes	Time	Call	Date	Follow-Up	Notes	Time	Call
2/12/19	test 1	aaaaa	3:30 PM	Past Due	2/12/19	test 1	aaaaa	3:30 PM	Past Due
2/13/19	test 4	dddd	4:00 PM	Past Due	2/13/19	test 4	dddd	4:00 PM	Past Due
2/13/19	test 3	cccc	5:00 PM	Past Due	2/13/19	test 3	cccc	5:00 PM	Past Due
2/14/19	test 2	bbbb	4:00 PM	Past Due	2/14/19	test 2	bbbb	4:00 PM	Past Due

<colgroup><col width="53" style="width: 40pt;"><col width="87" style="width: 65pt;"><col width="173" style="width: 130pt;"><col width="63" style="width: 47pt;"><col width="83" style="width: 62pt;"><col width="53" style="width: 40pt;"><col width="87" style="width: 65pt;"><col width="173" style="width: 130pt;"><col width="72" style="width: 54pt;"><col width="83" style="width: 62pt;"></colgroup><tbody>
</tbody>