# Returning array based on a search key - on two columns

I have attempted to use VLOOKUP and Array Functions so that when an Account is listed in G1, an array automatically is generated beneath it. The array formed (green) would only list rows where the Value in column D OR E equals the Account (G1).

Thanks for your help

## 2 Answers

Another option, using `query`

`=iferror(query(A1:E4,"select * where A is not null and (D ='"&G1&"' or E='"&G1&"')"))`

Sample

• Thanks. Can query be used for querying multiple tables and returning one array? e.g. say there were two sheets with similar information to columns A:E. Can a third sheet return a query array with data from the first two sheets? Commented Nov 3, 2021 at 12:01
• Yes. "Stack" query ranges by putting them in curly brackets `{}` and separate them with a semi-colon. `Select` is also different since it refers to Column Numbers rather than the Column letters. E.G. this formula `=query({wa_159964!A1:E4;wa_159964_02!A1:E4},"select * where Col1 is not null and (Col4 ='"&G1&"' or Col5='"&G1&"')")` takes data from two sheets and displays output on a third sheet. The query data can be in any columns on either sheet but there must be 5 matching columns of data on both sheets. Something similar can be done with `FILTER` can't it, @ErikTyler? Commented Nov 3, 2021 at 12:29

In G2:

`=IFERROR(FILTER(A:E,A:A<>"",ISNUMBER(SEARCH(G1,D:D&"~"&E:E))))`

This simply `FILTER`s in all items from `A:E` where there is data in the first column and where G1 can be found in a concatenated string formed of the two last columns' data separated by any symbol (which could have been any symbol that is certain not to turn up in the actual data, and which simply prevents the change of data bleed between the two pieces).