0

enter image description here

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 2

1

Another option, using query

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


Sample

screenshot

2
  • 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? 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?
    – Tedinoz
    Nov 3, 2021 at 12:29
1

In G2:

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

This simply FILTERs 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.