# Fix EXACT function so it always compares in the same row.

S

#### soilcon1

I am using the EXACT function in column B to compare if information in column
A is the same as column C on a spreadsheet. If it is not, I am inserting
cells above all the data in column C until I find a match with column A.
What is happening is that the EXACT function seems to stay fixed with it's
original cell in column C. Example:

A1=apple, C1=orange, B1=EXACT(A1,C1) results in FALSE

But, A2=orange and C1=orange, so I insert a cell above the cell in C so that
it becomes C2. So now:

A2=orange and C2=orange. But the EXACT function in column B in row 2 now
says:

B2=EXACT(A2,C3). C3=banna, so the result if False.

I want the function to remain B2=EXACT(A2,C2), NOT change to C3.

When more data is entered into the rows A and C later, I need to be able to
fill the EXACT formula down, so I don't think I can use \$. I don't want to
enter the function individually into each cell in column B.

Any ideas?

M

#### Max

To satisfy the process that you describe,
you could place this in B1:
=EXACT(OFFSET(INDIRECT("A1"),ROWS(\$1:1)-1,),OFFSET(INDIRECT("A1"),ROWS(\$1:1)-1,2))
Fill down as required. It'll return the results that you seek.

B

#### Bernie Deitrick

A macro would do it, but to use formulas, you can follow these
instructuions.

Insert two columns at column C, so that your original data in column C is
moved to column E. Then, in the new cell C1, use the formula

=IF(ISERROR(MATCH(ROW(),D,FALSE)),"",INDEX(E:E,MATCH(ROW(),D,FALSE)))

and in cell D1, use the formula

=IF(SUMPRODUCT(EXACT(\$A\$1:\$A\$100,E1)*1)>1,"There are " &
SUMPRODUCT(EXACT(\$A\$1:\$A\$100,E1)*1) & " EXACT matches for " & E1,
SUMPRODUCT(EXACT(\$A\$1:\$A\$100,E1)*ROW(\$A\$1:\$A\$100)))

Increas the row of the \$A\$100s to reflect your list in column A.

Then copy C1 down to match your list in column A, and copy D1 down to match

Then copy column C and past special values, and get rid of columns D and E.

HTH,
Bernie
MS Excel MVP

R

#### Ron Coderre

Try this:

C1: =EXACT(INDEX(A:A,ROW()),INDEX(C:C,ROW()))
Copy that formula down as far as you need

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

M

#### Max

C1: =EXACT(INDEX(A:A,ROW()),INDEX(C:C,ROW()))

Believe Ron meant to place the above formula in B1, not in C1 <g>

---

S

#### soilcon1

Ron's suggestion worked great. Yes, Max is right, the placement is in B1.
Thanks everyone!