Query on Strings

[waynedbrain]

Hi, This is my first post in mr excel, thanks to them they have such forum to share best practices.

I need help in one of my worksheet, i am no good in vba so i prefer formulas. Here is the scenario:

I have a list of words in column C and a set of long strings in column A. Results should reflect in column B.

So, i need a formula in column B to that would check whether eg. A1 contain one of the words in column C and show which word was found.

Column A contains
A1 the quick brown fox jumps over
A2 wayne is ugly and wasted
A3 I love cars and they
A4 Hunt the horse.

Column C contains
C1 wayne
C2 horse
C3 fox
C4 cars

Therefore, expected results should B
B1 fox
B2 wayne
B3 cars
B4 horse

Hope you can help me on this. Thanks peeps.

 

[Peter_SSs]

Welcome to MrExcel!

Would it ever be possible that 2 or more of the column C words appear in a single string in column A? eg "Wayne saw a fox"

If so, what would you expect in column B on that row?

Edit: Also what do expect if column A was "He has scars on his face" (contains "cars")?

 

[waynedbrain]

Welcome to MrExcel!

Would it ever be possible that 2 or more of the column C words appear in a single string in column A? eg "Wayne saw a fox"

If so, what would you expect in column B on that row?

Edit: Also what do expect if column A was "He has scars on his face" (contains "cars")?

Hi Mr Peter appreciate your reply.

Column C values are unique strings (actual values - Causes of issue: "high rise, low rise, Network behavior, processing...") and will therefore not be used repetitively in Column A (this column are engineer notes) and will only be encoded once.

This way easier for me to compile all similar codes.

 

[pgc01]

Hi
Welcome to the board

Try in B1:

=LOOKUP(2,1/SEARCH($C$1:$C$4,A1),$C$1:$C$4)

Copy/drag down

 

[waynedbrain]

Hi
Welcome to the board

Try in B1:

=LOOKUP(2,1/SEARCH($C$1:$C$4,A1),$C$1:$C$4)

Copy/drag down

Thank you master pgc01! this formula totally blew me away. i just want to ask 1 more thing as I need to understand the logic behind it.

for the lookup function the syntax requires
lookup_value, lookup_vector, [result_vector]
i understand the result vector part but why does lookup_value got a value of 2 instead of a cell and why is lookup_vector done in 1/SEARCH($C$1:$C$4,A1)

thanks.

 

[pgc01]

Hi

I'm glad it helped.

You usually use Lookup() in 2 cases

- With a lookup value and a lookup_vector in ascending order and you want to match the largest value in lookup_vector that is less than or equal to lookup_value.

- With a lookup value that is bigger than any value in the lookup_vector and you want to match the last non-error value.

I'm using it in the second case,

Search() returns either the position where the text is found {1,2,3,4 ...}, or an error, if it doesn't find it.

In the case of your example, when looking for {wayne;horse;fox;cars} in "the quick brown fox jumps over", it only finds the fox, in position 17. The result of 1/SEARCH(C1:C4,A1) is, therefore, {#VALUE!;#VALUE!;1/17;#VALUE!}.

Since the Search() returns a position, you know that 1/Search(), when the value is found, is always less or equal than 1 and that's why I use the lookup value 2, a value that is always bigger than any number in the lookup vector. I could have used any number bigger than 1.

So, in this case

=LOOKUP(2,1/SEARCH($C$1:$C$4,A1),$C$1:$C$4)

is

=LOOKUP(2,{#VALUE!;#VALUE!;1/17;#VALUE!},{wayne;horse;fox;cars})

The result is the value in position 3 of the result vector, "fox".

Hope it was clear.