VLookup without vba

Ted Penner
Ted Penner used Ask the Experts™
I have a sheet where I need to make a note next in one column based on the contents of another.


Column T contains a short list of items to be matched against the contents of Column B.  If Column B contains one or more instances of any tem in Column T, then the appropriate row or rows in Column S should be marked with the words Unreachable.

I need a VLookup solution that won't require any VBA.  

Assistance is greatly appreciated.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014
put this formula in S2

=IF(COUNTIF([Temp],LEFT([@[DNS Name]],5))>0,"unreachable","")
David MacDonaldChargé de projet processus d’affaires et systèmes TI
Hi there,
    try this formula in all cells of column S (The formula starts on S1 but do copy it in all cells of S):


Open in new window

The logic in it is if a value in S is found in one cell of column T VLOOKUP return the said value that is picked up by the IF statement that prints Unreacheable if the value was found.

When the value is not found, the S1=VLOOKUP fails because you cannot compare N/A with anything, hence the use of the IFNA formula.

Hope this helps.

You can do it with match function as well.

=IFERROR(IF(MATCH("*"&T2&"*",[DNS Name],0),"Unreachable",0),"")

OR Alternative you can use this one.

=IF(ISBLANK(T2),0,IFERROR(IF(MATCH(T2&"*",[DNS Name],0),"Unreachable",0),""))

Please see attached.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
The other suggestions are looking for the value in T2 as part of the contents of cell B2. In other words, they test on a row by row comparison.

I am guessing that you may want to search cell B2 for any of the names contained in column T. If so, your formula might be:
=IF(SUMPRODUCT(COUNTIF(B2,T$2:T$3 & "*")),"Unreachable","")

It is also possible that you might want to search column B for the name in cell T2. If so, the formula might be:
=IF(AND(T2<>"",COUNTIF(Table1[DNS Name],T2 & "*")>0),"Unreachable","")
Note that Excel amusant's suggestion provides a similar result, except it doesn't test if T2 is blank.
Ted PennerSoftware Engineer


All great solutions.  Thank you all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial