I am trying to query solely the IDs of clients that meet specific criteria from a source tab to an output tab and fill 2 columns with static values and 2 columns with dynamic values, based on criteria.
In the source tab I have:
+-----------------------+------+
| Status | ID |
+-----------------------+------+
| Retired/Deceased | 2a33 |
+-----------------------+------+
| Liquidation | 1sTR |
+-----------------------+------+
| Dissolved | 3B76 |
+-----------------------+------+
| Released from company | 463z |
+-----------------------+------+
| Active | 557g |
+-----------------------+------+
| In progress | zz34 |
+-----------------------+------+
| Demo | cc56 |
+-----------------------+------+
Please note, that there are 7 criteria values and I need the output for only 4 of them. Meaning that I need 4 values, based on which there will be binary fill of dynamic columns. The other 3 values are obsolete.
From these 4 values, if I have eg. Criteria 1, then I will have one fill of the 2 dynamic columns, if not (for the other 3 values) I will have other fill values.
So I guess simply going with a binary solution for the selection of the specific values is not applicable.
In the output tab logic:
+--------------------------------------------------------------------------------+
| Output tab |
+--------------------------------------------------------------------------------+
| ID | Status | Reason | Comment | Detail |
+----+--------+--------+----------------------------+----------------------------+
| A1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| B1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| C1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, else | then value 1, |
| | | | criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| D1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| E1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| F1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| G1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
Dummy output tab:
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Status | ID | Status | Reason | Comment | Detail |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Retired/Deceased | 2a33 | Unable to proceed | Unspecified | Retired/Deceased | Retired/No longer in business |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Liquidation | 1sTR | Unable to proceed | Unspecified | Liquidation | Retired/No longer in business |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Dissolved | 3B76 | Unable to proceed | Unspecified | Dissolved | Retired/No longer in business |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Released from company | 463z | Unable to proceed | Unspecified | (null) | No longer works for the company |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
The column 'Status' is not required. I added it just for reference and readability.
Apologies, but I have corporate security restrictions for sharing links to Google Sheets.
The part that I struggle most with is that in column 4 (the first one from the dynamic ones) needs to return the value of the criteria from column 2 from the source tab.
So far I have worked my way around the first part of the query, where I QUERY the IDs based on multiple criteria, labelled and filled the static values columns.
=QUERY(Data!$A$3:$BN,
"SELECT B, 'Unable to proceed', 'Unspecified'
WHERE A = 'Retired/Deceased'
OR A = ''Liquidation'
OR A = 'Dissolved'
OR A = 'Released from company'
AND A IS NOT NULL
LABEL 'Unable to proceed' 'Unspecified' , 'Status' 'Reason'", 1)
However, I am struggling with the dynamic columns, based on multiple criteria.
I looked up ARRAYFORMULA with IFERROR and VLOOKUP in a nested QUERY, but was not able to work my way around it.
Also, I am quite interested in how this would work if there were more than 2 options of values to fill columns 3 and 4 in the output tab.
As far as I know, the way to work around 2 values based on criteria is to nest an IFERROR function to make it binary. But what if there were more than 2 values to fill the arrays with?
