Tuesday, March 24, 2015

yogi_Conditionally Format Rows In Column A Where Name Does Not Match Any Of The Names In Table In Sheet 'NAMES'

                                           Google Spreadsheet   Post  #1929
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 24, 2015
question by running_nexus5:
Index/match or vlookup in this instance?
Hi everyone, I'm hoping someone here can help me figure this out. Here's my spreadsheet:

What I have is data from a couple of different sources about people, and occasionally I'll need to cross-check between sheets. But because sometimes the names aren't an exact match (sometimes the sources use a different name for the same person) I maintain a "NAMES" sheet to record name variations. In this example, DATA1 uses the name Joseph Richards and DATA2 uses the name Joe Richards. As you can see, NAMES shows that "Joe Richards" is an alternate name of Joseph Richards, so they are a match. I'm trying to identify records in DATA2 where the person is not found in DATA1 after cross-checking the alternate names in NAMES.

In this example I want to somehow flag Joel Richards as a record in DATA2 because he is not found in DATA1 even after cross-checking NAMES. Joe Richards (DATA2) would not be flagged because he is found in DATA1 after cross-checking NAMES.

Hopefully I have explained this sufficiently. If anyone here can help me out I'd be eternally grateful! :)
following is one way using Conditional formatting approach