Index/Match Array issue

[Mike Howarth]

Hi

I am trying to return a value based on multiple criteria using the index match function, but one of the criteria will need to return the latest date.

The data is as below. I want to return the value in Column B, where the Item code matches and the Modified date in D is the most recent. So for example, the first code A6AMU0463A305 should return 7.95.

The formula I have been trying to use is:

=INDEX(B:B,MATCH(1,(K12=A:A)*(MAX(D:D)),0),0)

where K12 is the Style code.

Thanks

Mike

[#2]

I have done the below now but it is still giving #N/A.

{=INDEX(B:B,MATCH(1,(A:A=K12)*(MAX(D:D)=D:D),0),0)}

If I use the evaluate formula function, I can see that the array works ok for the Style code as it returns true, but values in D are still just returning false.

Any ideas?

Thanks

Mike

[#3]

Hello Mike,

I tried to rebuid your problem, while I was doing it exactly the same way you did it I got the same error.
Then I was tired of the many values in debugging and converted the data to a table, changed the formula and it worked just fine.

=INDEX(B2:B7;VERGLEICH(1;(Tabelle1[item]=K12)*(MAX(Tabelle1[date])=Tabelle1[date]);0);0)

(German Excel here, so MATCH is VERGLEICH and ; are used instead of ,)

[#4]

I have no solution so far, but IMHO, both above shall not work if the maximum date in D:D (or in Table1[Date]) is out of range with searched item records. Here are nested arrays - first we have to define records with searched item (they could not be sequential in initial array), and within that range find the record with maximum date. 

[#5]

Hello Sergei,

you are right. When I change a date of an item that is not queried to be the max I get an #NV error, too. The original data looked like there was the same dates for every item. I assumed that was the general case in the original problem. If it is not, neither path works.

Jens

[#6]

Mike, please try this array formula

=INDEX(B:B,MATCH(1,($K$12=A:A)*(D:D=MAX(IF(A:A=$K$12,D:D))),0),0)

[#7]

Thanks All

That last one seems to work.

KR

Mike

[#8]

Great. I didn't explain the logic, hope it's understandable