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]

I'm not sure if I am following you correctly, but, it looks like you are needing a formula on the "results sheet"; sheet 2
Under the column title; "Time"

If so, I have a formula to try...
But first I need to clarify where the columns are on Sheet 2.

Assuming that the "Time" column is in column "I"
And the date column is in column "F" on that same page.

And assuming that the actual data begins on row 2.

And assuming that sheet 3 is called: " Sheet3 "

Put this array formula in the cell: " I3 "

=IFERROR(LARGE(($F3=Sheet3!$J$3:$J$1000)+(Sheet3!$K$3:$K$1000),IF(COUNTIF(Sheet3!$J$3:$J$1000,$F3)=1,1,(COUNTIF(Sheet3!$J$3:$J$1000,$F3)+1)-COUNTIF($F$3:F3,$F3))),"")

And as is the case with array formulas, to enter the formula, hold Ctrl + Shift + Enter in order to activate it.

Then copy it on down as far as you need to.
And change the ranges if you need to extend them further.

 

[jgold20]

Just to give you a little more info. Sheet 2 is pulling the information from sheet 3. Below are the formulas from sheet 2

SHEET 2

Date	Follow-Up	Notes	Time
=IFERROR(INDEX(Input_Events,sheet3!L3,2),"")	=IFERROR(INDEX(Input_Events,sheet3!L3,1),"")	=IFERROR(INDEX(Input_Events,sheet3!L3,4),"")	=IFERROR(INDEX(Input_Events,data!L3,3),"")
=IFERROR(INDEX(Input_Events,data!L3,2),"")	=IFERROR(INDEX(Input_Events,data!L3,1),"")	=IFERROR(INDEX(Input_Events,data!L3,4),"")	=IFERROR(INDEX(Input_Events,data!L3,3),"")

<colgroup><col width="53" style="width: 40pt;"><col width="87" style="width: 65pt;"><col width="173" style="width: 130pt;"><col width="55" style="width: 41pt;"></colgroup><tbody>
</tbody>

 

[jgold20]

New formula, not working - Time is now blank for the cells?

=IFERROR(LARGE(($Z6=data!$J$3:$J$102)+(data!$K$3:$K$102),IF(COUNTIF(data!$J$3:$J$102,$Z6)=1,1,(COUNTIF(data!$J$3:$J$102,$Z6)+1)-COUNTIF($Z$6:Z6,$Z$6))),"")

Sheet 2 Z6. AA6. AB6. AC6

Date	Follow-Up	Notes	Time
2/10/19	Test 1	AAAA
2/10/19	Test 2	BBBB

<tbody>
</tbody>

 

[Chrisdontm]

Perhaps this will work for you;

( This is skipping Sheet 3 )

But using array formulas, so enter all of these formulas with Ctrl + Shft + Enter

On Sheet 2 in the cell under the "Date" heading put:

=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A1)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),3),"")

On Sheet 2 in the cell under the "Follow-up" heading put:

=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A1)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),1),"")

On Sheet 2 in the cell under the "Notes" heading put:

=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A1)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),2),"")

On Sheet 2 in the cell under the "Time" heading put:

=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A1)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),4),"")

 

[jgold20]

All cells are now blank. Some question about the new formulas:

1) Why the row reference to $A$1:A1
2) Shouldn't the formulas on sheet 2 be referencing their corresponding cells (columns) on sheet data:
a) date - Column G
b) follow-up - Column F
c) Notes - Column I
d) Time - Column H

Thank you

 

[Chrisdontm]

1) Why the row reference to $A$1:A1
It is not the ROW reference, it is the ROWS function.
ROW would find the row number, ROWS counts the number of rows within the given range.
The ROWS($A$1:A1) equals one row.
Once that goes down to the next row the first $A$1 will remain and the second A1 will change to A2,
Which will give you the answer 2 , because it is then 2 rows in the range.
That formula is being used within the SMALL function.
SMALL asks for a range of numbers and then asks, do you want the smallest or next to smallest...etc...
We are using the ROWS formula to tell it to give us the smallest date and time combined;
In other words, the oldest date with the oldest time connected to it.

2) Shouldn't the formulas on sheet 2 be referencing their corresponding cells (columns) on sheet data:
Yes they should, and yes they are...
All of the formulas are the same because they are all stemming off of the combination of Date plus time.
So it is INDEX-ing the named range of: Input_Events , which is 4 columns wide.
The row part of that formula is determined by the SMALL function,
and the column is referenced near the end of the equation.
a) date looks for column: 3
a) follow-up looks for column: 1
a) Notes looks for column: 2
a) Time looks for column: 4

All along I have been recreating your worksheet so I could make sure it works.
On my end, it works.
The only reason I can think of without seeing your sheet is that the formulas are not being entered correctly.
Some folks are not familiar with array formulas. Just to make sure that is not the problem, lets review...

Once you have pasted a formula in a cell, you usually hit enter and that activates the formula.

But when you have a formula with a range that is comparing another range, (i.e. multiplying, dividing, adding or subtracting.),
It is a lot going on at once in a cell. So EXCEL requires you to enter it differently.
So once you paste the formula, or whenever you double-click in a formula to edit it, then
you have to hold down the Control Key and the Shift key at the same time and hit the Enter key, then let go of everything.

Then you can copy that formula on down with just copy and paste.

Let me know if all of this doesn't do it...

 

[jgold20]

Below is the formula I have in column Z6 (Date) on sheet 2:
=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A1)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),3),"")

Below is the formula I have in column AA6 (Follow-up) on sheet 2:
=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A1)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),1),"")

Below is the formula I have in column AB6 (Follow-up) on sheet 2:
=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A1)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),2),"")

Below is the formula I have in column AC6 (Time) on sheet 2:
=IFERROR(INDEX(Input_Events,MATCH(SMALL(IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102)),ROWS($A$1:A2)),IF((data!$H$3:$H$102+data!$I$3:$I$102)>0,(data!$H$3:$H$102+data!$I$3:$I$102))*1,0),4),"")

All of the formulas are arrays and have been copied down. I have a Mac and the key sequence is ctrl, shift, enter. I see the brackets in the front and the back.

The cells are blank

 

[Chrisdontm]

I found something about array formulas on a mac.

They mention a couple of different possible ways to do it.

One of the things said was to highlight the area first...check it out and see if something helps...

Be sure to read the replies.

Otherwise, we can make few helper columns to get the same results.


https://answers.microsoft.com/en-us...xcel-mac/e178f9a2-bb3e-4a06-8c2b-e9ff96ffca10

 

[jgold20]

In order for me to define the formula as an array, I have to highlight by dragging the cursor across the entire formula and than ctrl, shift, enter. This is the only way to get the brackets in the front (before the = sign) and the end (after the last )). Any other key sequence will not give me the brackets. The only fast test I can think of at this time is to define the array on a windows PC.

Any thoughts?

Thanks for all of your help