INDEX MATCH to seek the latest information

**sampahmel** · 05-04-2015, 10:33 AM

Hi all,

I am presented with a problem as detailed in the attached excel file.

My question is as follow:

When given only 2 pieces of information, which are ID and Subject, how do I go about to retrieve the LATEST score?
Take John for example. His Math score is listed twice on 2 different dates. What formula can I come to rely to retrieve the 99% score for Math on 3/1/2015?

Tried Index Match combo but wasn't able to figure out how to get the LATEST score?

Thanks.

**Ali Kırksekiz** · 05-04-2015, 10:43 AM

H3 = 25 (ID)
I3 =Math (Subject)

For J3 cell

Please Login or Register  to view this content.

**sampahmel** · 05-05-2015, 09:58 AM

I could use Ali's Lookup formula to obtain the desired result.

But I couldn't understand why the formula work as intended even though I understand how the Lookup formula works.

My understanding of Lookup formula is =LOOKUP( lookup_value, lookup_vector, [result_vector] ). More specifically for my question, why is there a "2" in the LOOKUP formula. And why does the 1/($B$3:$B$13=H3)*($F$3:$F$13=I3) work in place of the lookup_vector?

Update 2:

I noticed a flaw in the formula provided by Ali. When I look for (ID = 8) & (Subject = Italian), it failed to generate the result I was looking for.

**Ali Kırksekiz** · 05-05-2015, 10:23 AM

You must add extra parenthesis

Please Login or Register  to view this content.

**Ali Kırksekiz** · 05-05-2015, 10:29 AM

Originally Posted by sampahmel

My understanding of Lookup formula is =LOOKUP( lookup_value, lookup_vector, [result_vector] ). More specifically for my question, why is there a "2" in the LOOKUP formula. And why does the 1/($B$3:$B$13=H3)*($F$3:$F$13=I3) work in place of the lookup_vector?

Follow the links below for more information

http://www.pcreview.co.uk/threads/ho...6-d41.3729826/