# Filter a list of lottery results for those matching at least 4 numbers

I have a list of 3,000 winning lottery numbers. Each winning lottery number is on its own row and is comprised of 6 individual numbers, each in their own column. The winning number's draw date is also included.

Example:

I have 6 cells in which to enter lookup numbers. I would like to return all winning lottery numbers that match at least 4 of the lookup numbers.

For example, if my lookup numbers were `7, 15, 23, 45, 56, 59` then a list containing all winning lottery numbers that matched at least 4 of those numbers would be returned.

I have the following formula working so far, but it is missing the ability to restrict based on 4 or more matched numbers.

``````=QUERY(RESULTADOS!A:U;
"SELECT * WHERE (
C like '"&A1&"' OR D like '"&A1&"'
OR E like '"&A1&"' OR F like '"&A1&"'
OR G like '"&A1&"' OR H like '"&A1&"')
OR (C like '"&B1&"' OR D like '"&B1&"'
OR E like '"&B1&"' OR F like '"&B1&"'
OR G like '"&B1&"' OR H like '"&B1&"')
OR (C like '"&C1&"' OR D like '"&C1&"'
OR E like '"&C1&"' OR F like '"&C1&"'
OR G like '"&C1&"' OR H like '"&C1&"')
OR (C like '"&D1&"' OR D like '"&D1&"'
OR E like '"&D1&"' OR F like '"&D1&"'
OR G like '"&D1&"' OR H like '"&D1&"')
OR (C like '"&E1&"' OR D like '"&E1&"'
OR E like '"&E1&"' OR F like '"&E1&"'
OR G like '"&E1&"' OR H like '"&E1&"')
OR (C like '"&F1&"' OR D like '"&F1&"'
OR E like '"&F1&"' OR F like '"&F1&"'
OR G like '"&F1&"' OR H like '"&F1&"')";1)
``````
• Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented May 31, 2023 at 19:11

Use `filter()`, `byrow()` and `match()`, like this:

``````=filter(
A6:U20,
byrow(C6:U20, lambda(row,
4 <= counta(iferror(filter(row, match(row, A3:F3, 0))))
))
)
``````

See filter(), byrow() and match().

• Simply splendid! it worked perfectly and the return was even faster. Thanks! Commented Jun 2, 2023 at 0:25