Forum Discussion
IF AND Query
Hi, Sorry if this is basic, but I'm still learning.
This works - MIN(IF(Data!G:G=Answer!A2,(TIMEVALUE(Data!M:M))))
GG and A2 are names
This works - MIN(IF(Data!L:L=Answer!H1,(TIMEVALUE(Data!M:M)))
LL and H1 are dates
But when I combine them as follows it doesn't - MIN(IF(AND(Data!L:L=Answer!H1,Data!G:G=Answer!A2),(TIMEVALUE(Data!M:M))))
Thoughts?
In Excel, the AND function does not work as expected within an array formula (which is what you're using with MIN(IF(...))). This is because AND evaluates to a single value (either TRUE or FALSE) for the entire array, rather than for each row individually.
To solve this, you need to use the multiplication * or a combination of conditions directly in the IF function to create an array that checks each condition row by row.
=MIN(IF((Data!L:L=Answer!H1)*(Data!G:G=Answer!A2),TIMEVALUE(Data!M:M)))
My answers are voluntary and without guarantee!
Hope this will help you.
- Amazing - Thanks
Variants are
=MIN( FILTER( TIMEVALUE(Data!M:M), (Data!L:L=Answer!H1)*(Data!G:G=Answer!A2) ) )and
=AGGREGATE(15,6, TIMEVALUE(Data!M:M)/(Data!L:L=Answer!H1)/(Data!G:G=Answer!A2),1)- Thanks
