Need help with Conditional Formatting or Fomula

[BZ-Boy]

Hello

I am trying to use conditional formatting or a formula of some sort to highlight cells based off of another Tab & columns values if they match.

So, we have a phone number list showing all of our companies phone numbers on Tab#1 column A. Then on Tab#2, we have a list of phone numbers in column A, and in column B the cells show a persons name if the #'s are assigned to someone or blank if they are not assigned.

Considering we have approximately 8,000-10,000 phone numbers, it would be nice to use conditional formatting or a formula to determine whether or not a phone number is assigned or not assigned.

What I am trying to accomplish is creating the simplest way (whether it be conditional formatting or a formula) to reference Tab #1 column A and if the telephone numbers find a match on Tab #2 column A and do not show as being assigned, I would like them to either be highlighted and/or show as "Unassigned" for the value. I've used conditional formatting before on something much more complex, but cannot remember how I did it and can not find the original spreadsheet to look at.

Any help would be much appreciated!

I am attaching an example with some sample values.

Thanks in advance!!!

Tab1.jpg
Tab2.jpg

[Aardigspook]

1. Select your range on Sheet 1 (A2:A15000 for example).
2. On the Home tab of the ribbon, click 'Conditional Formatting', then 'New Rule', then 'Use a formula to determine which cells to format'.
3. In the formula box, enter this:

Please Login or Register  to view this content.

4. Click 'Format' then choose how you want the unassigned numbers to be highlighted (fill colour / font colour / etc).

Hope that helps.


Edit: if you prefer a formula on Sheet1, put this in B2 (or C2 or wherever you want it) then drag down:

Please Login or Register  to view this content.

[BZ-Boy]

Thanks so much for the reply. I am trying to go the formula route in the second example you provided but cannot get it to work. If the #'s match on Sheet 1 Row A to Sheet 2 Row A, then If there is an assigned name I need the name to show. If it is unassigned then I need it to show as unassigned or blank...Any ideas?

thanks again!

B

[Aardigspook]

Okay, if you want it to show 'unassigned' put the VLOOKUP formula in instead of the final "".

Please Login or Register  to view this content.

If you would rather have the name column blank instead of 'unassigned', then you can just use the VLOOKUP formula itself:

Please Login or Register  to view this content.