VLOOKUP question

[ranebow]

I have employee initials in spreadsheet "A". In spreadsheet "B" I have initials and names. I'm going to do a VLOOKUP from "A" to "B" to put the names in spreadsheet "A".

Now comes the problem. Spreadsheet "B" contains ALL employees....including new hires.

When doing the VLOOKUP is there any way to mark the employees in "B" that I've gotten a hit on? That way I can tell who isn't on "A" so I can add them.

 

[AnotherHiggins]

Do a VLookup the other way.

Example:
On sheet B enter something like
=if(iserror(vlookup(A2,sheetA!$a$2:$b$50,2,0)),"Missing","")

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[ranebow]

I thought about doing that. I just didn't want to have to do two. I was being lazy. Was hoping there was a way I could do it all at once. This will work.

Thanks!

 

[AnotherHiggins]

Glad to help.

Remember: a formula in a cell cannot affect any other cell.

One additional idea, though.... If this is going to be an ongoing process, you could use a VLookup in a Conditional Formatting statement on SheetB, then have the cells change fill color when the initials aren't found on SheetA.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[ranebow]

I've never done that in a conditional formatting statement. Now I MUST go play & see what I can do!

THANKS!

 

[AnotherHiggins]

HINT: You can't use references to another sheet in a Conditional Formatting formula, so you will have to use a Named Range that includes the column containing the initials on SheetA.

Feel free to ask questions if you run into problems with it.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.