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...
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.
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)
Thanks All
That last one seems to work.
KR
Mike
Great. I didn't explain the logic, hope it's understandable
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
