Lookup function to cell containing multiple values

[Tony D]

Hello,

Have searched extensively for this topic and coming up with no previous results.

Example:

A1 = 123

B1 = 123/456/789


Am looking for a lookup formula that will find the contents of A1 in B1.
A vlookup doesn't work because B1 contains multiple values.

If it matters, cell B1 will always have values separated by "/".

Thank you very much in advance!

 

[Claus Busch]

Hi Tony,

Am Mon, 18 Aug 2014 06:29:47 -0700 (PDT) schrieb Tony D:

A1 = 123

B1 = 123/456/789

try:
=MATCH(TRUE,FIND(A1,B1:B1000)>0,0)
and enter the formula with CTRL+Shift+Enter


Regards
Claus B.

 

[Tony D]

Hi Tony,



Am Mon, 18 Aug 2014 06:29:47 -0700 (PDT) schrieb Tony D:






try:

=MATCH(TRUE,FIND(A1,B1:B1000)>0,0)

and enter the formula with CTRL+Shift+Enter





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thank you Claus for your prompt reply!
Is there any suggestion to do this without an array? I would like to copy the formula down a column to determine a series of instances whether the value is found in my range. I have done this with a vlookup so far but this obviously found only matches that are on a one-to-one basis.

Much appreciated!

 

[Claus Busch]

Hi Tony,

Am Mon, 18 Aug 2014 06:55:51 -0700 (PDT) schrieb Tony D:

Is there any suggestion to do this without an array? I would like to copy the formula down a column to determine a series of instances whether the value is found in my range. I have done this with a vlookup so far but this obviously found only matches that are on a one-to-one basis.

try in C1:
=ISNUMBER(FIND($A$1,B1))
and copy down


Regards
Claus B.