Comparing 2 colums in 2 sheets

StepanieB · #1 07-04-2009, 07:31 AM

Hello guys,
my name is Stephanie from London and I have a very short, but urgent question. It sounds stupid, but I caint come to a solution.

I have to analyse 2 Spreadsheets with massiva Data (about 35 Tausand lines each) to see if the colums match each other.

My question is there any way/ function/ vlookup or anything else of seeing if

Spreadshead "thisone": Column A and Column B matches with
Spreadsheet "thatone": Column J and Column K.

With the result: True or False

I would be very, very grateful for your help.

Stephanie.

zbor · #2 07-04-2009, 07:39 AM

Hi Stephanie from London,

if I understood well you want this:

=ISNA(MATCH(A1;Sheet2!$A$1:$A$31;0))

But be carfull, this will give you FALSE if there is name in other sheet, and TRUE if there isn't.

$A$1:$A$31 is range on other sheet, you can put anything you need.

windknife · #3 07-04-2009, 11:34 AM

Try this
C1 =SUMPRODUCT(--(thatone!J1:J40000=A1),--(thatone!K1:K40000=B1))

Copied down.

HTH,
windknife

martindwilson · #4 07-04-2009, 03:39 PM

another possibility if you want to find if any two adjacent cells match anywhere in the other sheet
=ISNUMBER(MATCH(A1&B1,INDEX(Sheet2!$J$1:$J$16&Sheet2!$K$1:$K$16,0),0))

Welcome to the Excel Forum

Forum Statistics