May 08 2017
- last edited on
Jul 25 2018
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:
where K12 is the Style code.
May 08 2017 06:07 AM
I have done the below now but it is still giving #N/A.
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.
May 08 2017 06:27 AM
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.
(German Excel here, so MATCH is VERGLEICH and ; are used instead of ,)
May 08 2017 06:53 AM
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.
May 08 2017 07:19 AM
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.