google-sheetsgoogle-sheets-query
IF statement inside QUERY in Google Sheets
I have a google sheet where I have several projects listed. With a QUERY function I am retrieving information into another sheet for reporting purposes.
The formula I am using is: =QUERY($A$2:$D$8,"IF(OR($C2=1,$C2=2,$C2=3), Select A, B, D, Select A, B, C)",0)
What I am struggling to do is placing an IF statement inside the QUERY function.
I would like to get the Date 2 (Col4) value if the Status (Col2) value is "1", "2" or "3". Otherwise, if the Status (Col2) value is "4", "5" or "6" I would like the Query to return the Date 1 (Col3)
Any help would be appreiated. Thanks!
Solution
The formula would be like this:
=ArrayFormula(query({A2:B8,if(B2:B8<=3,D2:D8,C2:C8)},"select *"))
But the query isn't actually doing anything.
So you could just use:
=ArrayFormula({A2:B8,if(B2:B8<=3,D2:D8,C2:C8)})
- In Google sheets how can I Script the manipulation of the default Zoom?
- Google Sheets IMPORTDATA for xlsx files
- conditional formatting to highlight the highest value in a row compared to all the columns before it
- Google sheets summation formula, iterating until a new header on column A
- Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block
- How to scroll to bottom of a google spreadsheet using selenium
- Calculate average days between dates continuously updating
- Conditional formatting in Google Sheets is recognizing some cell values that equal 0, not others
- Model / Year formula fails when a sequence starts stops then starts again
- Google Sheets: Sumif formula
- Randomize cells in Google Sheets
- Google script for move rows depending on checkbox - Google sheet
- How do I splitTextToColumns only for cells containing commas in Google Apps Script?
- Getting a “Google hasn’t verified this app” warning when creating a trigger - ok to ignore it?
- Count in specific ways in Google sheets
- Fixing 1 row, and leaves each following row movable
- How to pass a range as a function parameter?
- Auto pulling a value in a formula to the next value in Google Sheets
- How to Extend a Formula to Apply to All Rows Using MAP or ARRAYFORMULA?
- Google Sheet - copy range if match
- shortest formula to get the last non zero value from column
- Copying data from a sheet to another with conditions
- How can I automatically generate due dates in Google Sheets based on payment plans?
- If one cell IS NOT EMPTY put text on another cell in Google Sheets
- How can I create a simple duplicate detector?
- Authorization is revoked every week. How to maintain persistent authorization?
- Conditional borders in Google Sheets
- Automatically adding a row to a sheet with data from another sheet once every day
- Adding borders based on grouped rows and conditional formatting in Google Sheets
- Illegal Argument Error Thrown When Trying to setTextStyle() for Rich Text in Google Apps Script