I have data like this:
| prof | date1 | date2 | date3 | date4 | date5 |
|---|---|---|---|---|---|
| date1 | date2 | ||||
| prof1 | tarif1 | tarif2 | |||
| date1 | |||||
| prof2 | tarif3 | ||||
| date1 | date2 | date3 | date4 | date5 | |
| prof3 | tarif4 | tarif5 | tarif6 | tarif7 | tarif8 |
How can I get this answer with one arrayformula (most recent tariff per prof)
| prof | tarif |
|---|---|
| prof1 | tarif2 |
| prof2 | tarif3 |
| prof3 | tarif8 |
I tried to resolve that problem by 2 steps :
to get the last column
=query(query(arrayformula(split(flatten(A1:A&"~"&B1:F&"~"&column(B1:F1)),"~",1,0)),"where Col1 is not null and Col2 is not null"),"select Col1,max(Col3) group by Col1 label max(Col3) ''")
then to retrieve the value
=offset($A$1,match(H1,A:A,0)-1,I1-1)
Is there a solution to do within one single arrayformula?
CodePudding user response：
See if this helps
=INDEX(regexextract(trim(transpose(query(transpose(filter(A:F, len(A:A))),,9^9))), "^(.*?)\s.*?([^\s] $)"))
If there are spaces in the values in the range A:F you'll have to replace those with another character (and do another substitute to add those spaces back in).