V Lookup and Conditional Formatting

**Robthfc** · 05-24-2010, 09:18 AM

Hi Guys,

Hope you are all ok.

I require some help with my limited knowledge of Excel.......I know what I want is physically possible, I just dont know how to implement it. And after hours of staring at the screen on Friday afternoon I gave up.

Basically I want to conditionally format the results my VLOOKUP finds. I work for a mobile phone company and need to report of connections and disconnections and re-activations to accounts.

My VLookup is comparing MPN (mobile phone number) from Sheet 1 to Sheet 2 and returning a result of either DISCONNECTION, Pending Disconnection, Re-activation or connection.

IF(ISNA(VLOOKUP(A2,Sheet2!A2:L348,4,FALSE)),"",VLOOKUP(A2,Sheet2!A1:L348,4,0))

What I want to do is conditionally format this so say the lookup returns ''DISCONNECTION'' i want that returned as a particular colour ie Blue and the same with the other results. The colours are obviously irrelevant.

I know I need to use a formula in conditional formatting..........I just can't work out the formula.

Can anyone help?

**Cheeky Charlie** · 05-24-2010, 10:43 AM

I don't think you do, really need to use a formula. Just select the column with your results, Press Alt + o, d (bring up conditional formatting dialogue) then you need:
Cell value (default)
equal to
DISCONNECTION
click [format...]
select flavours
[ok]
[ok]

Job done?

HTH

**Robthfc** · 05-25-2010, 11:21 AM

Thanks Charlie,

I have done it this way for the time being but after spending so long on it I am just wondering if anyone can help me with the formula that I was trying to create for four hours on Friday :O)

**NBVC** · 05-25-2010, 11:27 AM

Probably:

=VLOOKUP(A2,Sheet2!A1:L348,4,0)="DISCONNECTION"

**Cheeky Charlie** · 05-25-2010, 12:15 PM

Or, in between:
formula is:
=A1="DISCONNECTION"
HTH

**NBVC** · 05-25-2010, 12:20 PM

Yes, for sure that, and your original are the best ways, ... mine was simply, just to satisfy the quandary about incorporating the Vlookup() function in the CF

**Cheeky Charlie** · 05-25-2010, 01:01 PM

Indeed, as per your signature

=VLOOKUP(indirect(lookup(2+("black"="white"),{1,2,3},{"b","a","d"}) & len(right("good",2))),Sheet2!A1:L348,4,0)="DISCONNECTION"

I'm just being silly, I do understand that yours is the most validating of the OP's original intent.