Selecting Pairs of Numbers

Hey, I'm looking for a formula that will allow me to select Pairs (of numbers) for a group of numbers. Each number is in a different Column. See example below..


9 1 8 2
3 3 6 3
7 4 1 5
9 5 5 2
0 1 5 6
3 0 6 4
4 3 1 0
0 3 2 7

What i want to do is select any row that has the pair (in any order) 91 and/or 63 and/or 54

As you can see there is a number of rows that does not have either of the stated Pairs.

When you say "Select" what do you mean exactly ?

Do you mean "Select" aesthetically (eg Conditional Formatting) or physically (VBA) ?

In terms of Conditional Formatting - a very basic rule would be:

Please Login or Register  to view this content.

There is a bug with Conditional Format Array Formulae in XL2007 so best to keep as simple as possible (if a little long winded)
The IF approach is simply to minimise no. of COUNTIF calculations being performed (ie pairs only tested in prior pairs not found) - else you could just add the various results

When filtering in this context it is IMO best to work with a single column

Either:

a) populate a single adjacent column with the formula provided previously and subsequently filter for 1 in that column

or

b) create a "key" of concatenated numbers (with delimiter) in a single adjacent column (ranked smallest to largest) - you can then use a filter on the "key" column looking for *^1stnumber^*^2ndnumber^*" with OR repeating for each pair.

I would say Option a) is simpler (and offers greater scope for additional pairs)