VLOOKUP in Conditional Formatting

Hi All

I have a formula I want to use in conditional formatting, which is:

=IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)

I want to be able to then drag this formula down so that it applies to the rest of the row. Values would then be red if the result is true. The problem I have is that although B7 and I7 are not absolute references, they do not update as you drag down the formula.

So in row 8, the formula still reads:

=IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)

when i would like it to read:

=IF(VLOOKUP(B8,$C$38:$D$99,2,0)<>I8,1,0)

Can anyone help me get around this? I have tried using define name for the vlookup formula but that still treats the cell references as absolute.

Thanks everyone!

Brokovich.

Thanks Carim, but I have tried that. The problem is the references do not change, as explained.

Thanks anyway.

Brokovich.

Yes, it does copy the formula down, but it copies it exactly as it is on the previous cell. It copies as:

Row 7 =IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)
Row 8 =IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)
Row 9 =IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)

What i want it to do is copy like this:

Row 7 =IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)
Row 8 =IF(VLOOKUP(B8,$C$38:$D$99,2,0)<>I8,1,0)
Row 9 =IF(VLOOKUP(B9,$C$38:$D$99,2,0)<>I9,1,0)

If you try it you will see it doesn't work.

Brokovich.