Highlighting Data in Two Seperate Excel Spreadsheets

**zzapapa** · 06-07-2015, 08:04 PM

Hi there all! New here, Excel rookie but I try...

Ok. Here is some context. I have two spreadsheets from two different vendors. On each spreadsheet is a list of names, employee ID#, etc of employees who have used a particular service (same service from both vendors). I need to highlight people who are on both spreadsheets and thus using both services.

Can anybody give me a lay-styled explanation of how I can do this? Would this be a VLOOKUP?

Any help will be greatly appreciated!

Cheers,
zzapapa

**FDibbins** · 06-07-2015, 08:19 PM

Hi, welcome to the forum

I would normally use Conditional Formatting for this, but that does not work across workbooks

So, here is another approach. Assume your names (in each WB) are in column A, put this in the 1st sheet, B1, and copy down...
=IFERROR(MATCH(A1,'[Sheet Example.xlsx]data2'!F$2:F$18,0),"")

Adjust the sheet names for the other file.

This will give you a blank cell where there is NO match, and a row number, where there IS a match.

If you want, we can color the "found" cells

**zzapapa** · 06-07-2015, 08:31 PM

I have to admit, that looks amazing. Can you explain to me conditional formatting? I could move all the data onto one spreadsheet.

I'm literally going to have this page up tomorrow at work. I'll let you know how I progress!

**FDibbins** · 06-07-2015, 08:58 PM

See the attached. I have mocked up 2 columns of data.

Column A has Conditional Formatting in it to change colors where a match is found
Column C just has a formula in it to show the matches

For the CF...
1. highlight the range you want to apply the conditional formatting to (A3:A20 in my example)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =MATCH(A3,B$3:B$20,0)