Structured References and MATCH function

Question

Excel 2010.

I have a table (MyTable) with Column headers Col1, Col2... Col5, Chk1, Chk2

The Col1 through Col5 has raw data and Chk1 and Chk2 columns have some formulae.

For example Chk1 column has a formula to check whether the value of cell(current_row, Col1) is duplicate of any other value in the same column Col1.

The formula used is

=IF(NOT(ISNA([@Col1])),IF([@Col1]<>"",IF(MATCH([@Col1],[Col1],0)<ROW(),MATCH([@Col1],[Col1],0),"No"),""),"")

The above formula returns incorrect value - does not correctly identify the duplicate values

The same formula without using structured references but using old style A:A,A2... gives correct answer - example:

=IF(NOT(ISNA(A2)),IF(A2<>"",IF(MATCH(A2,A:A,0) < ROW(),MATCH(A2,A:A,0),"No"),""),"")

Any help what is wrong the way strctured reference formula was used?

Answer 1

Try this modification,

=IFERROR(IF(MATCH([@Col1],[Col1],0)<ROW(1:1),ROW(1:1),"No"),"")

I've forced the row comparison by starting at ROW(1:1) which grows to 2, 3, etc as it is copied down. Since it is a true representation of what the table row number is, it can be used instead of duplicating the MATCH() statement. The IFERROR() wrapper will handle both #N/A errors and blank cells that produce #N/A errors.

Answer 2

Excellent explanation on the intricacies behind structured references. I appreciate the same.

I changed the formula (since I wanted first duplicate occurrence row - excel row and not data row) I revised it as:

=IFERROR(IF(MATCH([@Col1],[Col1],0)<ROW(1:1),MATCH([@Col1],[Col1],0)+ROW([#Headers]),"No"),"")

Thanks again!

Answer 3

The following,

MATCH([@Col1],[Col1],0)

... will always return a row number that is less that ROW() since it is returning the row number of the match within [Col1]. The only way that this would actually work is if there were no header labels in the table which I find unlikely. Put another way,

=MATCH(A2, A2:A10, 0)

... in B2 will always return a number less than the row (row 2) it is on since it starts counting the row to return at row 2 and not row 1. Your second A1 style reference formula works because you are using A:A for the range_lookup in the MATCH(). This starts counting at row 1.