Index, Match, Max, If

[reberryjr]

I'm using this array formula to return the max date where the primary key matches in 2 different worksheets, and it works fine.
{=MAX(IF(Specs!C:C='Info'!C2,Stats!H:H))}

I'm trying to get the value in column J on the Stats sheet, for the same line. I've been looking at this for a few hours. I've googled and googled, searched through this forum and 2 others. While I've found some information, I've not been able to tweak the formula to get the appropriate value. I want the formula to be dynamic if possible, because the data in the Stats sheet is dynamic.

I've tried these formulas, but none of them work:
{=INDEX(Specs!J:J,MAX(MATCH(Specs!C:C='Info'!C:C,Specs!H:H)))}
{=INDEX(Specs!J:J,MATCH(MAX(Specs!H:H),Specs!H:H,0))}
{=INDEX(Specs!J:J,MAX(MATCH(IF(Specs!C:C='Info'!C2,Specs!H:H),Specs!C:C,0)))}

The 2nd formula returns a number, but it's not associated with the correct record.

The end state goal is to enter the formula into the sheet via a macro, but I have to understand how the formula works first. Any help is appreciated.

 

[Wad Mabbit]

HI,
I have this, and it works fine so far...
=MAX(INDEX(LLN_Data[Reading], MATCH(1, ($M194=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)))

LLN_Data[Reading are numbers
LLN_Data[Name is text
LLN_Data[Complete Time is date/time but may be as a text value

Sorry, I hit [POST} grrrrrr


Anyway, it returns the first hit in LLN_Data[Complete Time but as there can be a number of entries here with different days and times, I want to grab the latest.
There are also N/A entries in LLN_Data[Complete Time the raw data (pulled in from a CSV), which would mess up a simple max lookup, hence the criteria to ignore those.

I'm banging my head trying to use max in here. Have even tested with Value in case excel isn't picking that column up as a date, but arrrrgh my head hurts!

Oh, so far, the index and match works without making it an array, and I would like to keep it that way. The data I pull in only a few hundred rows so I can afford the intensity of an array, but I prefer not to. The rest of the book is quite complex and I want to keep it speedy.

Ignore column L, I'm just using that to pull the data from K as a value

Thanks in advance!

 

[Aladin Akyurek]

@ Wad Mabbit

Could you provide a small sample using XL2BB with the expected results added? No formulas, just data and expected results please.

 

[Wad Mabbit]

@ Wad Mabbit

Could you provide a small sample using XL2BB with the expected results added? No formulas, just data and expected results please.

 

[Wad Mabbit]

Hi, this is where I want information to land, in the LLN columns (while I'm 'here', this is next on the wishlist)
- How best to add an instance to a sum for each column where "Activity" is in the header row, and the cell content is either a "C", an "S" or "Co". From there, I want to turn it into a percentage of the sum of those columns to get the percentage of attendance for that item, say Activity, or Assessment, or Webinar...

Anyway, here is the main sheet

Tracking TEST.xlsm
	A							H				L	M	N	O	P	Q	R	S	T	U	V	W
1														B1 Induction				B1 LLN
2								Student				Sort	STATUS	D1 First Contact	D1 Follow-up	D1 Enrolled	D1 USI emailed	D1 Learning	D1 Reading	D1 Writing	D1 Oral Comm	D1 Numeracy	D1 LLN| Average
3								Messed up But later				Jo 02|06 Messed up But later	N	E	T			2	1	3	4	3	2.6
4								Wha Happened				Jo 02|06 Wha Happened	N	E	ML							3	0.6
5								Bilbo Baggins				[B1] Jo-01	P	C	T	Y	Y	3	3	4	4	3	3.4
6								Frodo Baggins				[B1] Jo-02	P	E	T	Y	Y	4	3	3	4	2	3.2
7												z
Tracking

Cell Formulas
Range	Formula
A1	A1	=COUNTA(M3:M126)
A2	A2	=VLOOKUP(SUBTOTAL(104,C3:C126),IF(SUBTOTAL(3,OFFSET(C3:C126,ROW(C3:C126)-ROW(C3),0,1)),B3:D127),2,0)
L3:L7	L3	=IF([@[ Date]]=0,"z", IF(OR(ISBLANK([@Student]),[@[ Manual Sort]]="N",[@[ Manual Sort]]="L"), LEFT([@[ Location]],2) & TEXT([@[ Date]]," DD|MM ") & [@Student], "["& [@[ Course]]&"] "& LEFT([@[ Location]],2) & "-" & TEXT([@[ Manual Sort]],"00") ))
M3:M7	M3	=IF($G3=trigger_B1,[@[ B1 STATUS]], IF($G3=trigger_B2,[@[ B2 STATUS]],""))
R3:R5,R7	R3	=IFERROR(INDEX(LLN_Data[Learning], MATCH(1, ([@Student]=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)),0)
S3:S5,S7	S3	=IFERROR(INDEX(LLN_Data[Reading], MATCH(1, ([@Student]=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)),0)
T3:T5,T7	T3	=IFERROR(INDEX(LLN_Data[Writing], MATCH(1, ([@Student]=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)),0)
U3:U5,U7	U3	=IFERROR(INDEX(LLN_Data[Oral Communication], MATCH(1, ([@Student]=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)),0)
V3:V5,V7	V3	=IFERROR(INDEX(LLN_Data[Numeracy], MATCH(1, ([@Student]=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)),0)
W3:W7	W3	=IF(COUNTA(TrackData[@[D1 Learning]:[D1 Numeracy]])=0,0,AVERAGE(TrackData[@[D1 Learning]:[D1 Numeracy]]))

Named Ranges
Name	Refers To	Cells
Key_Course	=Data!$T$3:$T$4	M3:M7
tblStudentRecord	=Tracking!$E$3:$FO$16	L3
trigger_B1	=Data!$T$3	M3:M7
trigger_B2	=Data!$T$4	M3:M7

The Sheet the data is from LLN Data follows in the next' reply"

 

[Wad Mabbit]

Hi,

Here is the LLN Data, a sheet I paste CSV values into from an LLN testing site

Tracking TEST.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Learner ID	First Name	Last Name	Email	Learning	Reading	Writing	Oral Communication	Numeracy	Complete Time
2	Messed up But later	skipped	Messed up	But later	X	2	1	3	4	3	2020-06-03 09:06:19	2020-06-03 10:07:10
3	Messed up But later	skipped	Messed up	But later	X	0	4	0	0	0	2020-06-03 10:06:70
4	Messed up But later	skipped	Messed up	But later	X	2	1	3	3	2	2020-06-03 08:06:14
5	Wha Happened	skipped	Wha	Happened	X	0	0	0	0	0	N/A
6	Wha Happened	skipped	Wha	Happened	X	0	0	0	0	0	N/A
7	Wha Happened	skipped	Wha	Happened	X	0	0	0	0	0	N/A
8	Wha Happened	skipped	Wha	Happened	X	0	0	0	0	0	N/A
9	Wha Happened	skipped	Wha	Happened	X	0	0	0	0	0	N/A
10	Wha Happened	skipped	Wha	Happened	X	0	0	0	0	0	N/A
11	Wha Happened	skipped	Wha	Happened	X	0	0	0	0	3	2020-06-03 07:06:56
12	Bilbo Baggins	skipped	Bilbo	Baggins	X	0	0	0	0	0	N/A	Bilbo Baggins	3	3	4	4	3
13	Bilbo Baggins	skipped	Bilbo	Baggins	X	0	0	0	0	0	N/A			3
14	Bilbo Baggins	skipped	Bilbo	Baggins	X	3	3	4	4	3	2020-06-01 06:06:04
15	Bilbo Baggins	skipped	Bilbo	Baggins	X	0	0	2	4	3	2020-06-01 05:06:85
16	Bilbo Baggins	skipped	Bilbo	Baggins	X	3	1	3	4	2	2020-06-01 03:06:81
17	Frodo Baggins	skipped	Frodo	Baggins	X	3	2	1	3	3	2020-06-10 09:06:49
18	Frodo Baggins	skipped	Frodo	Baggins	X	1	3	1	3	0	2020-06-10 08:06:81
19	Frodo Baggins	skipped	Frodo	Baggins	X	4	3	3	4	0	2020-06-10 08:06:37
LLN Data

Cell Formulas
Range	Formula
L2	L2	=MAX(VALUE(K2:K4))
L12	L12	=A12
M12	M12	=INDEX(LLN_Data[Learning], MATCH(1, ($L12=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]) * ("N/A"<>LLN_Data[Complete Time]), 0))
O12	O12	=INDEX(LLN_Data[Writing], MATCH(1, ($L12=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]) * ("N/A"<>LLN_Data[Complete Time]), 0))
P12	P12	=INDEX(LLN_Data[Oral Communication], MATCH(1, ($L12=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]) * ("N/A"<>LLN_Data[Complete Time]), 0))
Q12	Q12	=INDEX(LLN_Data[Numeracy], MATCH(1, ($L12=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]) * ("N/A"<>LLN_Data[Complete Time]), 0))
N12	N12	=INDEX(LLN_Data[Reading], MATCH(1, ($L12=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0))
N13	N13	=MAX(INDEX(LLN_Data[Reading], MATCH(1, ($L12=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)))
A2:A19	A2	=TRIM(C2) & " " & TRIM(D2)

 

[Wad Mabbit]

And while you are on the case ...

How best to add an instance to a sum for each column where "Activity" is in the header row, and the cell content is either a "C", an "S" or "Co". From there, I want to turn it into a percentage of the sum of those columns to get the percentage of attendance for that item, say Activity, or Assessment, or Webinar...

Tracking TEST.xlsm
	H					M										W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO
1																	1 Introduction				2 xxx				3 xxx					4 xxx						5 ccc					6 ccc						7 ddd						8 ddd					9 eee						10 fff						11 ggg						12 Congrats						REPORTING
2	Student					STATUS										D1 LLN| Average	D1 Resourced	D1 Activity	D1 Webinar	D1 Participation	D2 Resourced	D2 Activity	D2 Webinar	D2Participation	D3 Resourced	D3 Activity	D3 A| SAQ	D3 Webinar	D3Participation	D4 Resourced	D4 Activity	D4B| RP / Proj	D4C| Skills / Proj	D4 Webinar	D4MSMWHS200	D5 Resourced	D5 Activity	D5A| SAQ	D5 Webinar	D5Participation	D6 Resourced	D6 Activity	D6 Review	D6 B| Role Play	D6 Webinar	D6 FSKOCM07	D7 Resourced	D7 Activity	D7 Poster	D7 A| SAQ	D7 Webinar	D7 Participation	D8 Resourced	D8 Activity	D8 B| Proj	D8 Webinar	D8 FSKDIG03	D9 Resourced	D9 Activity	D9 A| SAQ	D9 B| Skills / Proj	D9 Webinar	10 FSKRDG05	10 Resourced	10 Activity	10 A| SAQ	10 B| Skills / Proj	10 Webinar	10 FSKLRG05	11 Resourced	11 Activity	11 A| SAQ	11 B| Case Study	11 Webinar	11 FSKLRG09	12 Resourced	12 Activity	12 Webinar	12 Participation	B1 App| Resulting	B1 aX| Report	B1 Activities	B1 Webinars	B1 Assessments	B1 Summary	B1 STATUS
3	Messed up But later					N										2.6																																																																						N
4	Wha Happened					N										0.6																																																																Y	01|06					N
5	Bilbo Baggins					P										3.4	TE	S	A	S	TE	R			TE	S	R		P	TE	R	R	R			TE	R	R			TE	R		R			TE	R		R			TE	R	R			TE	R	R	R																					1				P
6	Frodo Baggins					P										3.2	TE	S	A	S	TE	S	A	S	TE	S	S	A	S	TE	S	S	S	A	Co	TE	S	S	A	S	TE	S		S	A	Co	TE	S		S	A	S	TE	S	S	A	Co	TE	R	R	R																					1				P
Tracking

Cell Formulas
Range	Formula
M3:M6	M3	=IF($G3=trigger_B1,[@[ B1 STATUS]], IF($G3=trigger_B2,[@[ B2 STATUS]],""))
W3:W6	W3	=IF(COUNTA(TrackData[@[D1 Learning]:[D1 Numeracy]])=0,0,AVERAGE(TrackData[@[D1 Learning]:[D1 Numeracy]]))
AA3:AA6	AA3	=IF(AND([@[D1 Activity]]="S",[@[D1 Webinar]]="A"),"S", IF(OR([@[D1 Activity]]="C",[@[D1 Activity]]="F",[@[D1 Activity]]="S",[@[D1 Activity]]="P",[@[D1 Activity]]="A"),"P", "")) &IF(AND([@[D1 Activity]]="N",[@[D1 Webinar]]="N"),"N", IF(OR([@[D1 Activity]]="N",[@[D1 Webinar]]="N"),"!",""))
AE3:AE6	AE3	=IF(AND([@[D2 Activity]]="S",[@[D2 Webinar]]="A"),"S", IF(OR([@[D2 Activity]]="C",[@[D2 Activity]]="F",[@[D2 Activity]]="S",[@[D2 Activity]]="P",[@[D2 Activity]]="A"),"P", "")) &IF(AND([@[D2 Activity]]="N",[@[D2 Webinar]]="N"),"N", IF(OR([@[D2 Activity]]="N",[@[D2 Webinar]]="N"),"!",""))
AJ3:AJ6	AJ3	=IF(AND([@[D3 Activity]]="S",[@[D3 Webinar]]="A"),"S", IF(OR([@[D3 Activity]]="C",[@[D3 Activity]]="F",[@[D3 Activity]]="S",[@[D3 Activity]]="P",[@[D3 Activity]]="A"),"P", "")) &IF(AND([@[D3 Activity]]="N",[@[D3 Webinar]]="N"),"N", IF(OR([@[D3 Activity]]="N",[@[D3 Webinar]]="N"),"!",""))
AP3:AP6	AP3	=IF(AND([@[D3 A| SAQ]]="S",[@[D4B| RP / Proj]]="S",[@[D4C| Skills / Proj]]="S"),"Co", IF(AND([@[D4 Activity]]="S",[@[D4 Webinar]]="A"),"S", IF(OR([@[D4 Activity]]="C",[@[D4 Activity]]="F",[@[D4 Activity]]="S",[@[D4 Activity]]="P",[@[D4 Activity]]="A"),"P", "")) &IF(AND([@[D4 Activity]]="N",[@[D4 Webinar]]="N"),"N", IF(OR([@[D4 Activity]]="N",[@[D4 Webinar]]="N"),"!","")) )
AU3:AU6	AU3	=IF(AND([@[D5 Activity]]="S",[@[D5 Webinar]]="A"),"S", IF(OR([@[D5 Activity]]="C",[@[D5 Activity]]="F",[@[D5 Activity]]="S",[@[D5 Activity]]="P",[@[D5 Activity]]="A"),"P", "")) &IF(AND([@[D5 Activity]]="N",[@[D5 Webinar]]="N"),"N", IF(OR([@[D5 Activity]]="N",[@[D5 Webinar]]="N"),"!",""))
BA3:BA6	BA3	=IF(AND([@[D5A| SAQ]]="S",[@[D6 B| Role Play]]="S"),"Co", IF(AND([@[D6 Activity]]="S",[@[D6 Webinar]]="A"),"S", IF(OR([@[D6 Activity]]="C",[@[D6 Activity]]="F",[@[D6 Activity]]="S",[@[D6 Activity]]="P",[@[D6 Activity]]="A"),"P", "")) &IF(AND([@[D6 Activity]]="N",[@[D6 Webinar]]="N"),"N", IF(OR([@[D6 Activity]]="N",[@[D6 Webinar]]="N"),"!","")) )
BG3:BG6	BG3	=IF(AND([@[D7 Activity]]="S",[@[D7 Webinar]]="A"),"S", IF(OR([@[D7 Activity]]="C",[@[D7 Activity]]="F",[@[D7 Activity]]="S",[@[D7 Activity]]="P",[@[D7 Activity]]="A"),"P", "")) &IF(AND([@[D7 Activity]]="N",[@[D7 Webinar]]="N"),"N", IF(OR([@[D7 Activity]]="N",[@[D7 Webinar]]="N"),"!",""))
BL3:BL6	BL3	=IF(AND([@[D7 A| SAQ]]="S",[@[D8 B| Proj]]="S"),"Co", IF(AND([@[D8 Activity]]="S",[@[D8 Webinar]]="A"),"S", IF(OR([@[D8 Activity]]="C",[@[D8 Activity]]="F",[@[D8 Activity]]="S",[@[D8 Activity]]="P",[@[D8 Activity]]="A"),"P", "")) &IF(AND([@[D8 Activity]]="N",[@[D8 Webinar]]="N"),"N", IF(OR([@[D8 Activity]]="N",[@[D8 Webinar]]="N"),"!","")) )
BR3:BR6	BR3	=IF(AND([@[D9 A| SAQ]]="S",[@[D9 B| Skills / Proj]]="S"),"Co", IF(AND([@[D9 Activity]]="S",[@[D9 Webinar]]="A"),"S", IF(OR([@[D9 Activity]]="C",[@[D9 Activity]]="F",[@[D9 Activity]]="S",[@[D9 Activity]]="P",[@[D9 Activity]]="A"),"P", "")) &IF(AND([@[D9 Activity]]="N",[@[D9 Webinar]]="N"),"N", IF(OR([@[D9 Activity]]="N",[@[D9 Webinar]]="N"),"!","")) )
BX3:BX6	BX3	=IF(AND([@[10 A| SAQ]]="S",[@[10 B| Skills / Proj]]="S"),"Co", IF(AND([@[10 Activity]]="S",[@[10 Webinar]]="A"),"S", IF(OR([@[10 Activity]]="C",[@[10 Activity]]="F",[@[10 Activity]]="S",[@[10 Activity]]="P",[@[10 Activity]]="A"),"P", "")) &IF(AND([@[10 Activity]]="N",[@[10 Webinar]]="N"),"N", IF(OR([@[10 Activity]]="N",[@[10 Webinar]]="N"),"!","")) )
CD3:CD6	CD3	=IF(AND([@[11 A| SAQ]]="S",[@[11 B| Case Study]]="S"),"Co", IF(AND([@[11 Activity]]="S",[@[11 Webinar]]="A"),"S", IF(OR([@[11 Activity]]="C",[@[11 Activity]]="F",[@[11 Activity]]="S",[@[11 Activity]]="P",[@[11 Activity]]="A"),"P", "")) &IF(AND([@[11 Activity]]="N",[@[11 Webinar]]="N"),"N", IF(OR([@[11 Activity]]="N",[@[11 Webinar]]="N"),"!","")) )
CH3:CH6	CH3	=IF(AND([@[12 Activity2]]="S",[@[12 Interview]]="A"),"S", IF(OR([@[12 Activity2]]="C",[@[12 Activity2]]="F",[@[12 Activity2]]="S",[@[12 Interview]]="P",[@[12 Interview]]="A"),"P", "")) &IF(AND([@[12 Activity2]]="N",[@[12 Interview]]="N"),"N", IF(OR([@[12 Activity2]]="N",[@[12 Interview]]="N"),"!",""))
CK3:CK6	CK3	=SUM(COUNTIFS([@[D1 Activity]], {"P","S","Co"}))
CO3:CO6	CO3	=IF(ISBLANK($G3),"", IF(OR($E3="D",$E3="H"),"-", IF(OR($E3="L",$E3="P"),"!", IF($E3="N","N", IF(AND([@D4MSMWHS200]="Co",[@[D6 FSKOCM07]]="Co",[@[D8 FSKDIG03]]="Co",[@[10 FSKRDG05]]="Co",[@[11 FSKLRG09]]="Co"),"Co", IF(AND(P3="Y",AJ3="S",AP3="S",AU3="S",BA3="S",BG3="S",BL3="S",BR3="S",BX3="S",CD3="S",CH3="S"),"S", "P")&IF(ISBLANK([@[D1 USI emailed]]),"?","")&IF([@[D1 LLN| Average]]=0,"!","") )))))

Named Ranges
Name	Refers To	Cells
Key_Course	=Data!$T$3:$T$4	M3:M6
tblStudentRecord	=Tracking!$E$3:$FO$16	CO3
trigger_B1	=Data!$T$3	M3:M6
trigger_B2	=Data!$T$4	M3:M6

 

[Aladin Akyurek]

Please try to post a very small portion of your data and desired results (not formula results) using XL2BB.

 

[Wad Mabbit]

Hi, yeah, I had too much caffeine when I pushed ?.

Maybe if you just help me with a formula to return a match to a cell (a vlookup, of say, a name), where that name is repeated in the lookup range. The hit I'm after is that name with the most recent date in another column.

The lookup data is in a table.

The column with dates is [Complete Time]. The name column is [Name) and the column I want to pull a number from is [Reading].

E.g. I'm locating the latest reading literacy score of a candidate.


Thanks for your help.