VLookup without vba

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

Scenerio.

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.
Sample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014
Commented:
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
Commented:
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):

=IFNA(IF(S1=VLOOKUP(S1,T:T,1,FALSE),"Unreachable",""),"")

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.

David.
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.
Sample.xlsx
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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

Author

Commented:
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