Find word within text that matches range and Vlookup the result

Here, try this:

Formula:

Please Login or Register  to view this content.

However, this would return you wrong result if your criteria si within some other words.

For example:

"mobile assisted handover" will return you bird


P.S. Replace ; with , if you getting error

You are awesome! It works perfectly.

Yes, I am aware of this pitfall but it should be ok.

Can you help me understand the formula? I thought I am good with Excel but this makes me feel dumb

1st I don't understand why lookup_value is basically insensitive to input (any value gives me the same result).
2nd I have never seen slashes in range - how does it work ?

Thanks a lot

Originally Posted by snakeCZ

1st I don't understand why lookup_value is basically insensitive to input (any value gives me the same result).
2nd I have never seen slashes in range - how does it work ?

LOOKUP doesn't look for words. SEARCH is function that do that. If you use FIND instead of SEARCH then it will be sensitive to input (A vs. a etc).

Slashes are simple divisions. 1/2/3 is same as 1/6.

I will short it for explanations:

Formula:

Please Login or Register  to view this content.

SEARCH formula look for each word in range E3:E25 within cell B3.
If it find it it will return position in the text.

For example: Search A,B and M within word BALL will return 2; 1; #VALUE! respectively (B on 2nd position, A on first position, M is not found).

(Actually, now that I look it it would be same if you use without 1/.... but you would need to change number 2 into some very big number)

1/{2;1; #VALUE!} will convert those numbers into number less or equal to 1 and errors.
Since LOOKUP function is ignoring errors it will return LAST value in the range (1 in this case) and coresponding value in range F3:F25 (if defined).

This will work only if you define all values in range. Since all values are not defined (some are blank) you will get wrong result because finding those values is always true.
So I need to add one more criteria that will not include blank cells.

=LOOKUP(2;1/SEARCH($E$3:$E$25;B3)/($E$3:$E$25<>"");$F$3:$F$25)

Oh I see. You basically recreate lookup_vector (in case of salmon) to 1/{#VALUE!;7;#VALUE!;1;1...} then divide it by TRUE/FALSE (1/0) so you get errors to eliminate blanks. Therefore final vector looks like this {#VALUE!;0.1428;#VALUE!;#DIV/0!;#DIV/0!...}. Then the last question arises ... Does the lookup_value (2 from the formula) have to be always greater or same as number from the vector ?

It is unbelievable how one can learn so much everyday

Thanks a lot

You are correct.

Lookup_value in such cases must be always greater than greatest value.

In case you use 1/N where N is whole number so result is always less than 1. I've put 2 but you can put 2, 10 or 1000. doesn't matter.

But if you use only SEARCH and het first vector {#VALUE!;7;#VALUE!;1;1...} divided by TRUE/FALSE you would get {#VALUE!;7;#VALUE!;#DIV/0!;#DIV/0!...}

So you need to use some big number (because you never know position of the string).
Probably 1000 will be enough but it's common to use some even bigger numbers.
You will find in formula sometimes 9.99999999999999E+307 wich is largest allowed positive number.
Or something similliar like 99^99 (if you are lazy to write like me )

Notice also that if you find two (or more) matching strings like {#VALUE!;7;#VALUE!;#DIV/0!;#DIV/0!, 3, ...}
it will return latest.
So you will get the answer but be aware what could case it if it's unexpected.

Thanks zbor. You were very helpful

Glad I could help.
Hope to see more questions from you.
Or answers since that's best way to learn and practice