Conditional formatting inconsistent using MATCH in a custom formula

I have 2 cells A1 and B1 that contain the same values. I want to create a conditional formatting rule using the custom formula =MATCH(A1,B1) that colours both cells green.

This sometimes works on other cell pairs but other times it simply doesn't work.

Note that the cells have a custom number format that adds a letter and a semi-colon before the numbers.

Let me know what I am doing wrong.

3 pairs, 2 pairs worked, Q3 & R3 didn't:

My conditional formatting:

RULE

In the formula below, the MATCH function's search_type argument was changed to 0 (exact match) and it's range argument is now calculated by offsetting the search_key column by 1 or -1, depending if it is the left or right column in its column pair

# Apply to range
M3:R55

# Custom formula
=MATCH(M3,OFFSET(M3,0,IF(ISODD(COLUMN($M3))=ISODD(COLUMN()),1,-1)),0)

<br>

ISSUES

The main issue with your approach was tied to how relative refferences are handled. Additionally, the type of match was incorrectly set.

Relative References

The cell references in your formula are relative. When a conditional formatting rule is applied to a range of cells, relative references are updated for each cell based on its position in relation to the first cell (topmost-leftmost) in the rule's Apply to range.

Offsetting your formula's row references has no undesired effect, however, offsetting the column references by an odd number (every other) causes the MATCH function's range argument to reference a cell from a completely different column pair than was intended.

One can see the same behavior by placing the formula =MATCH(M3,N3,0) in M4 and then copying it to N4 and O4.

• In N4 the formula =MATCH(N3,O3,0) will not work as intended. It will attempt to match a value from N3 which is in the right column of a pair, with a value from O3, which is in the left column of a completely different pair.
• In O4 the formula =MATCH(O3,P3,0) will behave similarly to the original formula in M4. It will attempt to match the value from O3 which is in the left column of a pair, with a value from P3 which is in the right column of the same pair .

search_type

The original formula's MATCH function had the search_type argument set to 1 (default) which specifies searching the range for the largest value that is less than or equal to the search_key, while also assuming that the range is sorted in ascending order. It should have been set to 0 for an exact match.

The original formula was succeeding half the time, however the default search_type in combination with the actual data, was giving the false impression that it succeeding more than 50% of the time. The expected formatting would have been applied in some cases where the wrong cells were being compared, and where their values didn't match.

For example, the formula in M3 when applied to N3 becomes =MATCH(N3,O3) which would return TRUE in the rule because N3>=O3

=MATCH(N3,O3)   // search_type: 1         
=MATCH(58,8)    // 
=1              // 58 >= 8 (8 matched in position 1)

=MATCH(N3,O3,0)   // search_type: 0
=MATCH(58,8,0)   
=#N/A             // No Match found 

<br>

EXPLANATION

The conditional formatting rule is applied to a contiguous range of column pairs, with each pair comprised of a left and a right column, eg. M3:R55

1. If the MATCH function's search_key is located in a left column, its range argument should be the cell OFFSET one column right of the search_key.
   • OFFSET(M3,0,1)
2. If the search_key is in a right column, then the range should be the cell OFFSET one column left of the search_key
   • OFFSET(M3,0,-1)
3. The first column in the rule's Apply to range is necessarily a left column. If that column is odd-numbered, all left columns will be odd-numbered. If it's even-numbered, all left columns will be even-numbered.
4. Therefore, a test of the first column with the ISODD function, and the same test on the curent column, would necessarily both succeed or both fail if the current column is a left column.<br><br>
   For example,

# First Column is M, current cell is Q3

=ISODD(COLUMN($M3))=ISODD(COLUMN())
=ISODD(13)=ISODD(17)
=TRUE=TRUE
=TRUE        // current col is left


# First Column is M, current cell is R3

=ISODD(COLUMN($M3))=ISODD(COLUMN())
=ISODD(13)=ISODD(18)
=TRUE=FALSE
=FALSE      // current col is right


# First Column is N, current cell is R3

=ISODD(COLUMN($N3))=ISODD(COLUMN())
=ISODD(14)=ISODD(18)
=FALSE=FALSE
=TRUE       // current col is left

5. All that remains is to populate the MATCH function's range argument by passing the search_key as the cell_reference of an OFFSET function, and setting offset_rows to 0, and offset_columns to 1 or -1 depending on the TRUE or FALSE outcome of the ISODD tests comparison:

=MATCH(M3,OFFSET(M3,0,
  IF(ISODD(COLUMN($M3))=ISODD(COLUMN()),
    1,   // condition_true (current col is left)
    -1)) // condition_false (current col is right)
  ,0)

[<img src="https://i.stack.imgur.com/rf3Ut.png" width="600" />](https://i.stack.imgur.com/rf3Ut.png "click to enlarge")