Finding Last Test Date for a Student

[richh]

I have a large data table that was not very well put together. I need to find the last test date a student took a specific test in a range that contains also contains dates for the other tests they've taken. My table is as such:

ID | LName | Test Date | Math Test | Score | Eng Test | Score |
123| Smith | 01/01/19 | X________| 40__|
123| Smith | 02/01/19 | X________| 30__|
123| Smith | 03/01/19 | _________|_____| X_______| 75___|
123| Smith | 04/01/19 | X________| 50__|
123| Smith | 05/01/19 | _________|_____| X_______| 76___|

I need to find ID 123's last math test and its score. The row count on this data is into the tens of thousands, so doing this manually would be a nightmare! I know I can do a MATCH to find the first instance of the ID, but I don't know how to find the last. I'm thinking that if I find the first/last row of the ID, I can do a MAX on the range then do an INDEX MATCH on the ID and date to return the score. The problem I'm having is that the dates may be for other tests; the last date, 05/01/19, pertains to an English test, so it's not applicable to math.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	ID	Lname	Test Date	Math Test	Score	Eng Test	Score		ID	Last Math test
2	123	Smith	1/1/2019	x	40				123	4/1/2019
3	123	Smith	2/1/2019	x	30
4	123	Smith	3/1/2019			x	75
5	123	Smith	4/1/2019	x	50
6	123	Smith	5/1/2019			x	76
Sheet1

Cell Formulas
Range	Formula
J2	=LOOKUP(2,1/((A2:A10=I2)*(D2:D10="x")),C2:C10)

 

[richh]

That worked perfectly, thank you! Not really sure how that formula works, but it seems like a great one to have in the arsenal!

 

[kweaver]

Eric, when LOOKUP(2,1/((A2:A10=I2)*(D2:D10="x"))) returns a 1 (yes?), why does it pick the 2nd date from C2:C10?

 

[Eric W]

For everyone's enlightenment, here's how that formula works. It's a bit unusual, and I don't know if there's any "official" documentation for it. You can find places, including in this forum, that explain it though. LOOKUP has some built-in array processing. So when you do the (A2:A10=I2) part of the formula, you get an internal array back with all of the answers. In the example, it would be {TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE}. Then the (D2:D10="x") returns this array: {TRUE, TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE}. Then we multiply the 2 arrays together, each element times the corresponding element from the other array. Also, when multiplying TRUE/FALSE values, TRUE=1 and FALSE=0, so the result is {1,1,0,1,0,0,0,0,0}. So if you look at the array now, the 1's correspond to the rows where the ID is 123 and there is an X in column D.

The next thing we do is a clever trick someone came up with. We now take the reciprocal of the array. 1/1 = 1, and 1/0 returns a #DIV/0! error, so we get: {1,1,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}. The LOOKUP now looks like:

=LOOKUP(2,{1,1,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},C2:C10)

LOOKUP looks for a 2 in the array going from LEFT to RIGHT. (Internally the algorithm is more complicated, but that's the effect.) One nice thing about LOOKUP is that it ignores error values, which is the point of why we created the reciprocals. And the other thing is, if it never finds a match for the 2 (which it won't), it just returns the rightmost position. Then finally, it takes the value from the C2:C10 range in the same position.

Tricky, but very useful. Hope this helps!

 

[richh]

That explanation is downright beautiful, Eric. One may say it's..... EXCEL-lent! *ba dum cheesh*

 

[kweaver]

When "if it never finds a match for the 2 (which it won't), it just returns the rightmost position" -- it's not what I would think is the rightmost position...it's the 2nd position...is that what it's doing?

Since =LOOKUP(2,{1,1,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},C2:C10) is the partial result and since it cannot find a 2 in that array,
doesn't it try to find a 1? In my mind, there is a 1 in the FIRST position, which is why I asked about why it selected the second date.

So, the rightmost 1 is the 4th date in the date range. I guess I never realized it found the rightmost true value. Hmmm.

 

[kweaver]

Ran out of edit time...

When "if it never finds a match for the 2 (which it won't), it just returns the rightmost position" -- it's not what I would think is the rightmost position...it's the 4th position...is that what it's doing?

Since =LOOKUP(2,{1,1,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},C2:C10) is the partial result and since it cannot find a 2 in that array,
doesn't it try to find a 1? In my mind, there is a 1 in the FIRST position, which is why I asked about why it selected the second date.

So, the rightmost 1 is the 4th date in the date range. I guess I never realized it found the rightmost true value. Hmmm.