How to find if a value in array exists in a cell

[gilsa]

I have an array which contains a list of values, which I want to check if any of them appears in another cell.

let's say my array values are in A1:A4:
A1: 4.1
A2: 4.2
A3: 6.2
A4: 6.3

Now I basically want to search for each row I have the data in if one of the values of A1:A4 appears within another string.
The cell structure is something like test/domain/4.1/part1 (the location of the searched text is not fixed, can appear in different place).

In this case, since 4.1 appears in the string, it should return 4.1, if it can be found should return 'None.

Can someone help please?

 

[baitmaster]

if I understand you correctly, you have multiple text strings, each of which may contain one of those 4 array values

with a text string in cell I1, this formula should work for you: =SUMPRODUCT(--NOT(ISERROR(SEARCH($A$1:$A$4,I1)>0)),$A$1:$A$4)
Note that it returns "0" instead of "none"

 

[gilsa]

Thank you for your quick answer.
I assume the formula handles only numbers, but I missed one detail. The array I am referring to is not necessarily numbers (4.1 or 4.2), but most likely might either look like 4.1 or 4.1.1.

What will be the correct formula for such cases?

Cheers!

 

[baitmaster]

This formula will only work with numbers

I suspect there is a formula solution, but the only thing I can currently come up with will involve VBA

 

[Jonmo1]

Try

=IFERROR(LOOKUP(2^15,SEARCH($A$1:$A$4,I1),$A$1:$A$4),"None")

 

[baitmaster]

and there it is, although I still can't work out why

Jonmo, just tried playing with the Lookup formula with the OP data and found the lowest lookup value it will work with is 13. Can't see the significance of this? Could you explain how this is calculating / relevant please?

 

[Jonmo1]

I don't know the significance of 13, I don't know what 'OP Data' you're referring to, I only used what was in the first post, and added the 4.1.1

But there are a couple caveats to the formula.

1) There cannot be any blanks in A1:A4
2) If there are multiple matches for values in A1:A4 within I1, then it returns the one that appears last (furthest down) in A1:A4

 

[baitmaster]

OK thanks Jonmo

OP = Original Post, i.e. the same data you used. I thought it was doing something starting at the end and working up but just couldn't grasp exactly why. I'll file it away under "useful formulas that I can use but still not quite understand"...

 

[Jonmo1]

Here's how it works

=IFERROR(LOOKUP(2^15,SEARCH($A$1:$A$4,I1),$A$1:$A$4),"None")

2^15 = 32768
That just happens to be 1 larger than the character limit in a cell

SEARCH($A$1:$A$4,I1),$A$1:$A$4)
That creates an array of the results of each search using A1:A4
{SEARCH(A1,I1), SEARCH(A2,I1), SEARCH(A3,I1), SEARCH(A4,I1)}

So if say only A2 was found within I1, and it's at character #10 within that string
You get
{#VALUE!, 10, #VALUE!, #VALUE!}

So we have
=IFERROR(LOOKUP(32768,{#VALUE!, 10, #VALUE!, #VALUE!},$A$1:$A$4),"None")

Lookup ignores errors
Since 32768 is larger than any numeric value in the lookup array ({#VALUE!, 10, #VALUE!, #VALUE!})
Then it returns the last numeric value in the array, 10
10 is the 2nd position in that array
So the lookup returns the 2nd position of the lookup vector A1:A4, A2

Hope that helps.

 

[gilsa]

Many thanks. Works like a charm!