Conditional Format colour

[meridklt]

Is it possible to have the conditional formating in my attached file to say DISQ instead of whats there now if a match is not found?

[davesexcel]

Try iserror for vlookup, place this formula in column B
=IF(ISERROR(VLOOKUP(A1,E:G,2,FALSE)),"disq",VLOOKUP(A1,E:G,2,FALSE))
=IF(ISERROR(VLOOKUP(A2,E:G,2,FALSE)),"disq",VLOOKUP(A2,E:G,2,FALSE))
=IF(ISERROR(VLOOKUP(A3,E:G,2,FALSE)),"disq",VLOOKUP(A3,E:G,2,FALSE))
=IF(ISERROR(VLOOKUP(A4,E:G,2,FALSE)),"disq",VLOOKUP(A4,E:G,2,FALSE))
=IF(ISERROR(VLOOKUP(A5,E:G,2,FALSE)),"disq",VLOOKUP(A5,E:G,2,FALSE))

[meridklt]

Dave, I then get #NAME? and lose my original match of GB07L39311 a1:

BUY & SELL MEN #NAME? 02:05:14.700
P T DOBIE disq 02:05:15.200

[davesexcel]

Dave, I then get #NAME? and lose my original match of GB07L39311 a1:

BUY & SELL MEN #NAME? 02:05:14.700
P T DOBIE disq 02:05:15.200

#NAME error?
But it works for the second line,?
I'm sure you can see what the formula is supposed to do, try rewriting it in B1

here's more help on vlookup

http://www.contextures.com/xlFunctions02.html

[meridklt]

Thanks Dave, but all that lost me.

[Dean England]

meridklt,

If you look at the formulas in Daves post you will notice a space between the "u" and "p" in the second instance of Vlookup.

=IF(ISERROR(VLOOKUP(A1,E:G,2,FALSE)),"disq",VLOOKU P(A1,E:G,2,FALSE))


That space should not be there. It happens sometimes on this forum. Just remove that space and you should no longer get #NUM.

hth,

Dean

[meridklt]

Thanks Dean, works fine now.