Home > Mobile >  Google Sheets Array Formula Match on Multiple Conditions
Google Sheets Array Formula Match on Multiple Conditions

Time:05-04

I want to return the date from column F, according to the code in the resume table. However, if the column A code repeats itself, I want it to return the date (column f) closest to today's date.

I tried this formula, and it worked halfway - the ones in gray in the resume table are correct. But the ones in the red line are not.

[=ArrayFormula(unique(FILTER(if(A2=A2:A11;MAXIFS(F2:F11;A2:A11;A2);F2:F11);F2:F11<>""))))][1]

Well, I also thought about using an axillary column - column G, for example:

if cod (resume) = cod1 and if column G is greater than zero, return me the largest date (column F), but if column g less than zero, return me the smallest date (column F)

Anyway, I don't know if you understood very well...

Summarizing:

In the summary table, the rows that are in red, was supposed to return only the date '16/05/2022'

enter image description here

enter image description here


update:

=INDEX(IFNA(VLOOKUP(A16:A; 
 {SORT(FILTER(A2:F11; F2:F11< TODAY()); 6; 0); 
  SORT(FILTER(A2:F11; F2:F11>=TODAY()); 6; 1)}; 6; 0)))

enter image description here

  • Related