vlookup individual strings of text within a cell

**dougdrex** · 03-20-2013, 01:04 PM

I'm trying to search through a string of text to see if there are any matching values using the vlookup formula. If there are any matches, I need to return that matching value. If there are no matching values, I want to leave the field blank (""). Is this possible? If not, is there a better function to use to accomplish this?

I've attached the spreadsheet if that would help. The string of values to search for are in Sheet 1 (formulas supposed to go in column B). The values to search against are in Sheet 2.

Any ideas or help is GREATLY appreciated!

**Kevin UK** · 03-20-2013, 01:26 PM

Hi
Try in B3 on sheet 1 and copy down. The list on sheet 2 you could define a name, then use the named range in your formula.

Formula:

Please Login or Register  to view this content.

**Kevin UK** · 03-20-2013, 01:45 PM

Hi dougdrex

Here is a file with a dynamic range called List, just add to your list on sheet 2 and it will up date.
If you go to the formulas tab, click name manager, click on the formula in the refers to see the range.
hopefully this is what you require!

**dougdrex** · 03-20-2013, 03:32 PM

That's exactly what I need, thanks for the help!!

By the way, what does 20^20 mean?

**Pepe Le Mokko** · 03-20-2013, 03:36 PM

Originally Posted by dougdrex

That's exactly what I need, thanks for the help!!

By the way, what does 20^20 mean?

It's the equivalent of 20 exponent 20, let's say a very large number.

9.99999999999999E307 is often used because it's the largest number excel can handle