Hi,

Is there a function to check if a list of cells (say form a1 to a10)
contains a string in a cell (say b2)??

I try lookup and the problem of lookup is that even though b2 is not
in a1 to 10, it returns the previous result instead of N/A.

Thanks,
wing

Use VLOOKUP() instead of LOOKUP().
To find an exact match, the fourth argument of VLOOKUP() should be FALSE

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| Hi,
|
| Is there a function to check if a list of cells (say form a1 to a10)
| contains a string in a cell (say b2)??
|
| I try lookup and the problem of lookup is that even though b2 is not
| in a1 to 10, it returns the previous result instead of N/A.
|
| Thanks,
| wing
|

If you only want to know if it does contain that string (or maybe where the
match occurs), use MATCH rather than a lookup.

eg =MATCH(B2,A1:A10,0) will return "5" if the item in B2 is in A5 (the fifth
position in the searched list).

So =NOT(ISERROR(MATCH(B2,A1:A10,0)) will produce "True" if it is in the list
and "false" if not.
--
Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk


"(E-Mail Removed)" wrote:

> Hi,
>
> Is there a function to check if a list of cells (say form a1 to a10)
> contains a string in a cell (say b2)??
>
> I try lookup and the problem of lookup is that even though b2 is not
> in a1 to 10, it returns the previous result instead of N/A.
>
> Thanks,
> wing
>
>

Instead of:
=NOT(ISERROR(MATCH(B2,A1:A10,0))

you could also use:
=isnumber(MATCH(B2,A1:A10,0)



AdamV wrote:
>
> If you only want to know if it does contain that string (or maybe where the
> match occurs), use MATCH rather than a lookup.
>
> eg =MATCH(B2,A1:A10,0) will return "5" if the item in B2 is in A5 (the fifth
> position in the searched list).
>
> So =NOT(ISERROR(MATCH(B2,A1:A10,0)) will produce "True" if it is in the list
> and "false" if not.
> --
> Adam Vero
> MCP, MOS Master, MLSS, CWNA
> http://veroblog.wordpress.com
> http://www.meteorit.co.uk
>
> "(E-Mail Removed)" wrote:
>
> > Hi,
> >
> > Is there a function to check if a list of cells (say form a1 to a10)
> > contains a string in a cell (say b2)??
> >
> > I try lookup and the problem of lookup is that even though b2 is not
> > in a1 to 10, it returns the previous result instead of N/A.
> >
> > Thanks,
> > wing
> >
> >

--

Dave Peterson