Query to retrieve values from different columns, based on multiple criteria

Question

I need to create a QUERY function to retrieve values from different columns, based on the following 2 criteria:

If the value in column A is "Company name changed", I need columns B and C. And if the value in column A is "Email changed", I need to retrieve the corresponding values to that record from columns B, C, D and E. All of the above in the same table.

Please, excuse me for not sharing a dummy sheet, but I have corporate security restriction, which is not allowing me to make any sheet visible to anyone outside the company.

Source tab:

+----------------------+------+-------------+--------------------------+----------------------------+
|           A          |   B  |      C      |             D            |              E             |
+----------------------+------+-------------+--------------------------+----------------------------+
| Status               | ID   | Company     | Email                    | Generic Email              |
+----------------------+------+-------------+--------------------------+----------------------------+
| Email changed        | 223A | ABC Capital | [email protected]    | [email protected]        |
+----------------------+------+-------------+--------------------------+----------------------------+
| In progress          | 446g | DEF Finance | [email protected] | [email protected] |
+----------------------+------+-------------+--------------------------+----------------------------+
| Company name changed | 233A | GHI Dealing | [email protected]    | [email protected]      |
+----------------------+------+-------------+--------------------------+----------------------------+
| Dissolved            | 334s | JKL Brokers | [email protected]       | [email protected]         |
+----------------------+------+-------------+--------------------------+----------------------------+

Output tab:

+------+-------------+-----------------------+---------------------+
| ID   | Company     | Email                 | Generic Email       |
+------+-------------+-----------------------+---------------------+
| 223A | ABC Capital | [email protected] | [email protected] |
+------+-------------+-----------------------+---------------------+
| 233A | GHI Dealing |                       |                     |
+------+-------------+-----------------------+---------------------+

What could be the possible workaround for this?

player0 · Accepted Answer · 2019-02-21 14:27:14Z

1

={QUERY(A1:E, "select B, C, D, E 
               where A = 'Email changed'", 1);
  QUERY(A2:E, "select B, C, ' ', '  ' 
               where A = 'Company name changed'
               label ' ''', '  '''", 0)}

answered Feb 21, 2019 at 14:27

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query to retrieve values from different columns, based on multiple criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related