Search Data for matching row/array

Question

I want to search a set of data to see if it contains a matching set of values eg.

+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
| A    | B    | C    | D    |
+------+------+------+------+

would match the last row in

+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
| B    | C    | D    | A    |
| A    | D    | B    | C    |
| A    | B    | B    | D    |
| D    | C    | B    | A    |
| A    | B    | C    | D    |
+------+------+------+------+

A boolean return would be fine.

Any pointers would be greatly appreciated.

Cheers

So far I've tried concatenating the 2 data groups individually and then searching for the key that way but for some reason it fails even though I can see they contain identical strings. The output can just be a count as it will only match once. Thanks. — Chris Barrett
– Chris Barrett, Commented Aug 26, 2018 at 18:41

Grzegorz Mogilewski · Accepted Answer · 2018-08-26 21:05:35Z

1

Assumptions

Columns to check: A:D
Values to check are in F1:I1
Result in K1 - boolean

Code

=NOT(ISERROR(
    QUERY({ArrayFormula(A1:A&B1:B&C1:C&D1:D)},
    "select * where Col1='"&JOIN("",F1:I1)&"'",0)
        )
     )

Picture

answered Aug 26, 2018 at 21:05

Grzegorz Mogilewski

9626 silver badges8 bronze badges

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

1 Comment

Chris Barrett Over a year ago

Very nice. Thanks for the help.

Collectives™ on Stack Overflow

Search Data for matching row/array

1 Answer 1

Assumptions

Code

Picture

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Assumptions

Code

Picture

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related