AND condition added to Array Formula doesn't work as expected
I have the below array which has been working perfectly. It's comparing values from another sheet and if they match what is on this sheet , then it shows verified, otherwise it shows unverified.
=ArrayFormula(iferror(if(row(T:T)=1, "Certification Status",
if(A:A="","",
if(lower(vlookup(T:T,importrange("abc123","Course Completion!E:H"),2,0))
&"-"&lower(vlookup(T:T,importrange("abc123","Course Completion!E:H"),3,0))
&"-"&to_date(int(vlookup(T:T,importrange("abc123","Course Completion!E:H"),4,0)))=
lower(I:I)&"-"&lower(U:U)&"-"&F:F,"Verified","Unverified")))))
I needed to add a condition to evaluate if a row value contains "SIM" and the Attendance status equals "No" or Blank then show "Unverified", in addition to the other checks. If the row value does not contain "SIM" then use the original formula shown first. However when V:V= SIM and the attendance status equals no or blank, it still shows verified.
What did I do wrong here?
=ArrayFormula(iferror(if(row(T:T)=1, "Certification Status",
if(A:A="","",
if(and((U:U="SIM"),(V:V<>"yes"),"Unverified",
if(lower(vlookup(T:T,importrange("1A7U7OY8q4M5zCI3WLrwESMwV_471D_4kF71v30yA438","Course Completion (THINKIFIC)!E:H"),2,0))
&"-"&lower(vlookup(T:T,importrange("1A7U7OY8q4M5zCI3WLrwESMwV_471D_4kF71v30yA438","Course Completion (THINKIFIC)!E:H"),3,0))
&"-"&to_date(int(vlookup(T:T,importrange("1A7U7OY8q4M5zCI3WLrwESMwV_471D_4kF71v30yA438","Course Completion (THINKIFIC)!E:H"),4,0)))=
lower(I:I)&"-"&lower(U:U)&"-"&F:F,"Verified","Unverified"))))))
Solution
AND & OR are not supported under ARRAYFORMULA. use * & +
For OR:
if((V:V="SIM")+(lower(V:V)="yes")
If you need AND you can't have the same column in both cases so this is just example if second column is W and not V:
if((V:V="SIM")*(lower(W:W)="yes")
- Short circuit Array.forEach like calling break
- Passing a NumPy 3d array to a C function with a triple pointer as an argument
- how can i sort 2 dimensional array based on index array in PHP?
- Why does usort() not provide a stable sort for equally compared values under PHP8?
- Custom key-sort a flat associative based on another array
- Group rows of a 2d array by a column value and push whole rows as child elements per group
- Merge two multidimensional arrays to form parent-child relationships
- Sort the first and second level of a 2d array by numeric keys in DESC order
- Sort the parent level of a 3d array by a column in the deepest subarray descending
- Sort a 2d array by its keys which relate to custom sorting values in another 2d array
- Alternative or polyfill for Array.from on the Internet Explorer
- Sorting a php array of arrays by custom order
- Sort an array based on related data in a 2d array
- Sort an array by keys
- How to access data in a multidimensional array containing objects?
- Synchronously sort an array by sorting another other array
- How to define an array with conditional elements in react
- Create a new array of by removing the zero values from another array
- How to populate an array without duplicates while looping
- Assigning sort()'s returned value to the array variable replaces array with true
- sort() returns a true value instead of the sorted array
- rsort() is putting strings with 10 after strings with 2
- sort() puts alphanumeric string ending in 10 before another string ending in 2
- sort() returns true instead of the sorted array
- List all contiguous sub-arrays
- arsort() is not preserving the original element order for duplicated values (below PHP8)
- Why is accessing any single element in an array done in constant time ( O(1) )?
- Isolate values from multiple columns of a 2d array then sort the values
- Sort keys of an array
- Python - Printing a matrix w/o pprint