Latest Score Given

RossL

I am trying to return a score based on the date and contact. what I am looking to do is search my list of names and show what the last score each person was given. I can easily come up with the last date a score was given but I am stuck on how to get the score that is related to that person and date. 

Lauren B

I think and Index/collect function should work. 

Try this: =INDEX(COLLECT(Score$1:Score$11, Name$1:Name$11, Name14, Date$1:Date$11, Date14), 1)

 

This returns a value of 1 for Jane, 4 for Maine, etc

RossL

I tried that and it is returning a #INVALID VALUE

Lauren B

You might want to make sure you are pasting into correct cell. See screenshot where this formula works in my test.