Lookup function to cell containing multiple values

  • Thread starter Thread starter Tony D
  • Start date Start date
T

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!
 
Hi Tony,

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

B1 = 123/456/789
Click to expand...

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


Regards
Claus B.
 
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
Click to expand...

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!
 
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.
Click to expand...

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


Regards
Claus B.
 
Back
Top