Comparing columns of data

[Hankjam]

Hi

Would someone be kind enough to tell me what approach I should take if
I want to compare two columns of data. Say I have a column of 220
values and I want to see if any of a second column with 20 values are
represented in the first column and where they are.

Many thanks for your time.

Aj
Fife

 

[Guest]

Would someone be kind enough to tell me what approach I should take if
I want to compare two columns of data. Say I have a column of 220
values and I want to see if any of a second column with 20 values are
represented in the first column and where they are.

Hankjam

Assuming that column A has a header and Column B is the compare column, in
C2 type
=ADDRESS(MATCH(B2,$A$2:$A$500,1)+1,1,4)

if column A has no header try

=ADDRESS(MATCH(A1,$A$2:$A$500,1),1,4)

If column B (lookup column) contains items not in column A suppress #N/A#
values with

=IF(ISERROR(ADDRESS(MATCH(B3,$A$2:$A$500,0)+1,1,4)),"",ADDRESS(MATCH(B3,$A$2:$A$500,0)+1,1,4))

Regards
Peter

 

[Hankjam]

Hankjam

Assuming that column A has a header and Column B is the compare column, in
C2 type
=ADDRESS(MATCH(B2,$A$2:$A$500,1)+1,1,4)

if column A has no header try

=ADDRESS(MATCH(A1,$A$2:$A$500,1),1,4)

If column B (lookup column) contains items not in column A suppress #N/A#
values with

=IF(ISERROR(ADDRESS(MATCH(B3,$A$2:$A$500,0)+1,1,4)),"",ADDRESS(MATCH(B3,$A$2:$A$500,0)+1,1,4))

Regards
Peter

Hi Peter

thanks for that. I'll try it at work in the morrow.

Yours

Aj
Fife