Forum Discussion
Index/Match Array issue
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
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
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 ,)
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.
