Vlookup a cell if it contains a certain text (within a column of text)

Hi there,

I'd the the cell in Column A highlighted if it contains any of the cells in Column Z:

COLUMN A
A1 [email protected]
A2 <<[email protected]
A3 <[email protected]
A4 <Mailto: Sarah <[email protected]
A5 { <[email protected]
A6 <[email protected]
A7 [email protected]
A8 [[email protected]
A9 <[email protected]
A10 [/[email protected]
A11 _|[email protected]
A12 mailto:[email protected]


COLUMN Z
accounts@
admin@
booking@
careers@
enquiries@
enrol@
feedback@
info@
join@
media@
membership@
office@
order@
question@
reception@
request@
reserve@


ie. A1 would receive a highlighted formatting given it contains 'admin@', and although A4 contains other text and symbols, it too would receive a highlighted format because it contains 'enquiries@' in the cell.

I know how to do a vlookup but on partial text, i get a little confused

Any help would be greatly appreciated

hi Rob8489, do upload a sample Excel file next time so that we dont have to re-create your data. select the range you want to apply to (i.e. from A1:A12)
in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
in Excel 2003, go to Format -> Conditional Formatting -> Formula is:
=ISNUMBER(LOOKUP(2^15,SEARCH($Z$1:$Z$17,A1)))

format colour