Search code examples
sortinggoogle-sheetsfiltergoogle-sheets-formulavlookup

Google Sheets Array Formula Match on Multiple Conditions

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

Sheets

Solution

  • use:

    =INDEX(IFNA(VLOOKUP(A16:A; SORT(A2:F11; F2:F11; 0); 6; 0)))

    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