Why isn't Importrange() data working in function?
I've looked up every solution to this question and tried them all with no luck.
Im importing coordinates from another page and determining if they're between a range using the =if(and() function. But it seems like the and() will only accept 3 and() statements max. And some of the functions don't even register in the functions. I cant figure out why.
All cells are formatted as numbers tried various combinations
Feel free to play with the function. I've been working on it for 3 days and still can't find a solution. Can anyone help?
https://docs.google.com/spreadsheets/d/1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ/edit?usp=sharing
Take a look at the green higlighted rows. They all fall within the Green highlights range but nothing.,,
try:
=ARRAYFORMULA(IF(
IFNA(VLOOKUP(A2:A*1, SORT(QUERY(SPLIT(FLATTEN(IF(F2:G="",,F2:G&"♦"&E2:E)), "♦"),
"where Col2 is not null", 0)), 2, 1))=
IFNA(VLOOKUP(B2:B*1, SORT(QUERY(SPLIT(FLATTEN(IF(H2:I="",,H2:I&"♦"&E2:E)), "♦"),
"where Col2 is not null", 0)), 2, 1)),
IFNA(VLOOKUP(A2:A*1, SORT(QUERY(SPLIT(FLATTEN(IF(F2:G="",,F2:G&"♦"&E2:E)), "♦"),
"where Col2 is not null", 0)), 2, 1)), ))
!! however
in case of possible overlaps you will need to use this formula and drag down:
=ARRAYFORMULA(TEXTJOIN(", ", 1,
IF((A2*1>=F$2:F)*(A2*1<=G$2:G)*(B2*1>=H$2:H)*(B2*1<=I$2:I), E$2:E, )))
your importrange should be:
=ARRAYFORMULA(REGEXREPLACE(""&QUERY(
IMPORTRANGE("1ol7DTYZcwZVZk6WhfCLQ1JBt3_3QIEXEC5TkfEerCMw", "CheckinForm!I2:J30"),
"where not Col1 = '#ERROR!' and Col1 is not null", 0), "^: ", )*1)
