Check if a cell contains a part of the other cell

**onurkmetu** · 08-21-2012, 03:03 AM

Hi all,
I had been using the =Vlookup function to check if some numbers in one cell exactly appears on another cell. But now again I have numbers in one cell, but now the other cell has some text prefix (like tcn-12). When I use ctrl+f and search 12, excel finds that cell containing 12 (tcn-12). But using Vlookup, excel doesn't recognize 12 in the tcn-12 cell.
Is it possible to find the numbers, in the text-prefixed cells, and get those cells painted? (even if I can find them is enough for me)

Thank you very much in advance,

**Ace_XL** · 08-21-2012, 03:41 AM

How about..

=IFERROR(VLOOKUP("*"&number&"*",Range,1,FALSE),"")

**onurkmetu** · 08-21-2012, 08:17 AM

Hi, thanks for the comment, but I couldn't actually figure out how to put my data into your formula this time, I'm not so familiar with excel formulas, sorry.
=IFERROR(VLOOKUP(B:B;A:A;1;0)) what I'm trying to do is getting the column B looked up at column A, but of course this formula gives an error. can you please explain it more specificaly, sort of dummy version

**Ace_XL** · 08-21-2012, 08:41 AM

Something like this..

See Column H

**taps** · 08-21-2012, 09:21 AM

Hi,

Try the attached file and also check the conditional formula. L and M column is newly created based on which conditional formula is given in J column. You can modify it accordingly.