Analysing once cell to see if contains text present in a range of cells

[jb11216]

Hi there. I'm pretty knowledgeable with Excel but this one has me a bit confused. Hoping someone can guide me in the right direction as to which formula to use.

I have cell A1 - let's say the cell text is "example.com/SKU-123". I have a separate range of cells on another sheet, and somewhere in this range is the text "SKU-123".

What I want to do is write a formula in cell B1 that will look at the text in A1, and try to check it against the text in the range of cells on the other sheet. I need it to be able to look at an entire column and see if any text in that column, matches any part of the text in cell A1. So that if it finds any of the values in this other range, it will return the text - so that B1 says "SKU-123."

Is this possible? I've tried various iterations of ISNUMBER(SEARCH( but I'm not getting anywhere.

Any help greatly appreciated. Thank you!

[Glenn Kennedy]

Try:

=IFERROR(LOOKUP(1E+100,SEARCH(Sheet2!$A$1:$A$10,A1),Sheet2!$A$1:$A$10),"")

and remember, it's easier for us to help you if you provide a sample excel file.

[Punnam]

HI,
A sample work book with a example will be more effeticve to help ,
1) Do you have Single entry or duplicate " SKU-123 " , If Unique then you can use Wils card to search in Lookup function that is possible if you have duplicate they you may need array function

[jb11216]

OK sure. I've attached a sample file. I tried your formula (after amending it to fit the naming convention of my spreadsheet) and unfortunately it just returned zero.

So in this file, sheet "skus" column A is where I want to look, and sheet "plytix_export(2)" is where I need the value returned. So for example, Cell A2 of "plytix_export(2)" contains a string of text that is present in sheet "skus" column A somewhere - and I need to return that value.

Many thanks!

[Punnam]

Hi,

I request you to try soultion provided " Glenn Kennedy " that should work .

[jb11216]

Unfortunately this just returns a value of 0 for every line.

[Punnam]

you need to try using using Ctrl+Shift+Enter as this is array function and also chnage the range as per your data

[Glenn Kennedy]

It is NOT an array formula. In any event, there's no such thing in O365. Also, it does not return a zero. I suspect thta you did not change the ranges correctly. Hence the advantage of a sample sheet...

It does take some time to calculate, as your sheet is quite big.

[Punnam]

It is NOT an array formula. In any event, there's no such thing in O365. Also, it does not return a zero. I suspect thta you did not change the ranges correctly. Hence the advantage of a sample sheet...

It does take some time to calculate, as your sheet is quite big.

My Mistake, i am confused it is not Array function & also not checked MS office verson, i am sorry

[jb11216]

Thank you Glenn - this is perfect. Really appreciate your help.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.