lookup functions and text comparisons

[GK]

I'm trying to figure out the best way to compare information on multiple
worksheets and return corresponding values across mulitple columns.

Here are the details:
Sheet 1 (530 people listed)
Column A contains last names
Column B contains first names
Column C blank
Column D blank

Sheet 2 (536 people listed)
Column A contains last names
Column B contains first names
Column C contains division
Column D contains group

What I want to do is compare first and last names on sheet 1 with first and
last names on sheet 2 - Then return the corresponding values from column C
and D on sheet 2 onto sheet 1.

Next, I would like to identify missing names from sheet 1.

 

[Max]

Assume data in row2 down in both Sheet1/2

In Sheet1,
In C2, normal ENTER
=IF(COUNTA($A2:$B2)<2,"",INDEX(Sheet2!C$2:C$600,MATCH(1,INDEX((Sheet2!$A$2:$A$600=$A2)*(Sheet2!$B$2:$B$600=$B2),),0)))
Copy C2 to D2, fill down to the last row of source data in cols A & B.This
returns the required results from cols C & D in Sheet2. #N/A results will be
those where the last/firstnames (in Sheet1) are not found in Sheet2. You can
easily apply autofilter on either col C or D to filter out these cases for
whatever action.

Then in Sheet2,
In E2, normal ENTER
=IF(COUNTA(A2:B2)<2,"",--ISNUMBER(MATCH(1,INDEX((Sheet1!A$2:A$600=A2)*(Sheet1!B$2:B$600=B2),),0)))
Copy down to the last row of source data in cols A & B. Col E returns: 1 for
last/firstnames (in Sheet2) found in Sheet1, zero if not found. Easily apply
autofilter on col E to filter out the zero cases for whatever ensuing action
is required (eg to update Sheet1).


To improve robustness in matching (remove extra leading/trailing white
spaces, extra spaces in-between text), you can wrap TRIM like this, for
example in Sheet1's C2
=IF(COUNTA($A2:$B2)<2,"",INDEX(Sheet2!C$2:C$600,MATCH(1,INDEX((TRIM(Sheet2!$A$2:$A$600)=TRIM($A2))*(TRIM(Sheet2!$B$2:$B$600)=TRIM($B2)),),0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik