want to find two matching answers governed by two separate equatio

G

gallie

There are two columns of data in one spreadsheet. Each column is governned by
a different equation, and I want to know how to find any pair of matching
answers between the columns.
 
S

swatsp0p

Assuming your data is in Cols. A & B, in Col C enter this in row 1 and
copy down as far as needed. Adjust the range to meet your needs:

=IF(ISERROR(MATCH(A1,$B$1:$B$1000,0)),"","MATCH FOUND IN ROW
"&MATCH(A1,$B$1:$B$1000,0))

Column C will be blank until a match for that row in A is found in B.
Then the Row number that matches will be returned in the format "MATCH
FOUND IN ROW nn". Of course, if duplicate matches exist, only the
first one found will be returned.

NOTE: if your data range does not begin in row 1, adjust your formula
to account for this by adding row number minus one to the formula.
e.g. if your data starts in row 10, add 9 as such:
=IF(ISERROR(MATCH(A10,$B$10:$B$1000,0)),"","MATCH FOUND IN ROW
"&MATCH(A10,$B$10:$B$1000,0)+9)

Does this work for you?

Bruce
 
B

B. R.Ramachandran

Hi,

You could use conditional formatting.
Let's suppose that the data are in A2:A1001 and B2:B1001.
Select the entire data (i.e., A2:B1001) --> "Format" --> "Conditional
Formatting" --> in the drop down box under Condition 1, select "Formula Is"
--> in the formula bar enter =$A2=$B2 --> Click on "Format" button select
"Patterns" and select a color --> "OK" --> "OK".
This will color the cell pairs containing matching data.

OR

In column C2 enter the formula, =IF(A2=B2,"X",""), and fill-in the formula
down to the last row. The formula will return an "X" symbol wherever there
is a match between columns A and B.

Hope this helps,
Regards,
B. R. Ramachandran
 
Top