VLookup without vba

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
Ted PennerSoftware Solutions ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
put this formula in S2

=IF(COUNTIF([Temp],LEFT([@[DNS Name]],5))>0,"unreachable","")
David MacDonaldChargé de projet processus d’affaires et systèmes TICommented:
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.
Excel amusantCommented:
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
byundtMechanical EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ted PennerSoftware Solutions ArchitectAuthor Commented:
All great solutions.  Thank you all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
WordPress

From novice to tech pro — start learning today.