Arrayformula with index from a query

0
votes

My current formula is:

=INDEX(QUERY({$A$2:$P},"select max(Col"&COLUMN($M$2)&") where lower(Col"&COLUMN($D$2)&") contains lower('"& D3 &"')",1),2,1)

Here's the following logic I used:

  1. My customers have different equipments, which I have to do QA every year. On that Sheet, I have listed all the equipments from my customers.
  2. Col D has the name of my customers. Each customer has multiple records, one for each equipment they have.
  3. Col M has the date I last did QA on their equipment.
  4. I used the QUERY function to retrieve the last date I QA'd an equipment from that customer (using the max(Col M)).
  5. Used the INDEX function to retrieve just the value of that 'max date'.

What I would like:

I managed to do it just for cell D3, but I would like to expand it with an ARRAYFORMULA to D3:D. I tried applying with these 3 options below, but it keeps returning only one value:

 1. =INDEX(QUERY({$A$2:$P},"select max(Col"&COLUMN($M$2)&") where lower(Col"&COLUMN($D$2)&") contains lower('"& ARRAYFORMULA(D3:D) &"')",1),2,1)
 2. =ARRAYFORMULA(INDEX(QUERY({$A$2:$P},"select max(Col"&COLUMN($M$2)&") where lower(Col"&COLUMN($D$2)&") contains lower('"& D3:D &"')",1),2,1))
 3. =ARRAYFORMULA(INDEX(QUERY({$A$2:$P},"select max(Col"&COLUMN($M$2)&") where lower(Col"&COLUMN($D$2)&") contains lower('"& ARRAYFORMULA(D3:D) &"')",1),2,1))

How could I make it work?

1
google-sheets
Can you share a sheet with some sample data? It will be easier to understand what you are trying to do then. - Broly
Here you go: docs.google.com/spreadsheets/d/… - Gabriel Sabiá
Index when use with arrayformula will always return one row value, it is the restriction . You need to copy the formula to all the row you want in case you want to use index function, else try other formula - Kin Siang

1 Answers

1
votes

You can do this by looking up the value of the customer name to a sorted array of customer name and QA date.

If your data looks like this: enter image description here then use the formula =ArrayFormula(IF(LEN(A2:A)=0, "", (VLOOKUP(A2:A, QUERY(A2:B, "select A, B order by A, B desc"), 2, 0)))) in C2.

You will have to adjust the formula based on the column names in your sheet though.