0
votes

I need an ARRAYFORMULA, VLOOKUP or FILTER formula in K7 that:

Puts the date from A7:A IF the value in J7:J is >= E7:E AND <= F7:F AND D7:D = "Traverse"

I have got multiple solutions to work on the individual rows, but none to work as an array formula to effect the entire column.

=IF(AND(J7>=E7:E,J7<=F7:F,D7:D="Traverse"),OFFSET(J7,0,-9),"")

or

=IFERROR(FILTER(A:A,E:E<=J7,F:F>=J7,D:D="Traverse"))

or

=ArrayFormula(IFNA(vlookup(J7:J2064,Query(sort(filter({date("20"&left(A7:A,2),mid(A7:A,3,2),right(A7:A,2)),A7:G},D7:D="Traverse"),6,1,1,0),"Select Col6,Col2",0),2,1)))

an example sheet is here if anyone can shed any light :)

https://docs.google.com/spreadsheets/d/1SYbhfyIA2KoZF0k-TuIqm-Kn9L9o0I16EGbSXDaTABU/edit?usp=sharing

enter image description here

2

2 Answers

0
votes

use:

=ARRAYFORMULA(IF((J7:J>=E7:E)*(J7:J<=F7:F)*(D7:D="Traverse"), A7:A, ))

enter image description here

update:

=ARRAYFORMULA(IFNA(VLOOKUP(J7:J, SORT({
 FILTER({E7:E,  A7:A}, D7:D="Traverse"); 
 FILTER({F7:F+1,H7:H}, D7:D="Traverse")}), 2, 1)))
0
votes

Thanks for your help, in the end I managed to get a formula working from someone on the Google help forum. This is a working solution if you're interested:

=ArrayFormula(IFNA(vlookup(J7:J,sort({filter({Register!E7:F,Register!A7:A},Register!D7:D="Traverse")
;if({index(sort(filter({Register!A7:A,Register!E7:E,Register!F7:F},Register!D7:D="Traverse"),2,1),0,2);""}
>{"";index(sort(filter({Register!A7:A,Register!E7:E,Register!F7:F},Register!D7:D="Traverse"),2,1),0,3)},
{{"";index(sort(filter({Register!A7:A,Register!E7:E,Register!F7:F},Register!D7:D="Traverse"),2,1),0,3)}+1,
{index(sort(filter({Register!A7:A,Register!E7:E,Register!F7:F},Register!D7:D="Traverse"),2,1),0,2);""},
iferror({index(sort(filter({Register!A7:A,Register!E7:E,Register!F7:F},Register!D7:D="Traverse"),2,1),0,2)
;""}/0,"")},)},1,1),3,1)))