CODEWITHSUNDEEP
google-sheetsarray-formulasgs-vlookupgoogle-query-language
AlexShevyakov
AlexShevyakov

Reputation: 423

Using Google QUERY to return MAX value AND corresponding values from other columns

I have the following dataset (see image): Columns A to E contains WORK SITE inspections records (SITE ID (A), Inspection type (B), Site status (C), Inspecting Company (D) and Date of inspection (E)).

In the next Sheet (green zone from G to J) I have a sorted list of WORK SITES (Column G) for which I am trying to build a query expression to retrieve the LATEST date of inspection (=MAX value), CORRESPONDING Inspecting Company and CORRESPONDING Site status, WHERE Type of Inspection is = 'ANNUAL' i.e. B = 'ANNUAL'. In the example below, for the WORK_SITE_1 the desired result should be:

WORK_SITE_1 - 13/Nov/2019 - COMP_8 - Operational

I was hoping to use ARRAYFORMULA to avoid placing formulas into each row.

=ARRAYFORMULA(IF(G2:G="","",VLOOKUP(G2:G,A2:E,{5,4,3},FALSE)))

The above however gets me the first instance of the inspection date only and not the LATEST date. For the time being, I use the following QUERIES in each row H:

=QUERY($A$1:$E$14,"SELECT MAX(E) WHERE A = '"&G2&"' LABEL MAX(E) '' ",0)

and in Column I for each row

=QUERY($A$2:$E$14,"SELECT D, C WHERE E = date '"&TEXT(H2, "yyyy-mm-dd") &"' AND A = '"&G2&"' ",0)

I would appreciate a hint on this. Thank you in advance.

enter image description here

Upvotes: 1

Views: 1481

Answers (1)

MattKing
MattKing

Reputation: 7773

without getting too deep into it, i think you can just sort the range of the vlookup by date descending and it'll return the latest one.

=ARRAYFORMULA(IF(G2:G="","",VLOOKUP(G2:G,SORT(A2:E,5,FALSE),{5,4,3},FALSE)))

Upvotes: 1

Related Questions