Identify if the result contains a particular value
 

Hi,

VLOOKUP supports wildcards, so if you replace $A3 with

"*"&$A3&"*"

you have "contains"

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sarah (OGI)" wrote:

I've got the following formula:

=IF(VLOOKUP($A3,Lookup!$A:$K,9,FALSE))=16,"Yes","N o")

However, I'd like the formula to now see if the result of the vlookup
contains (not equals) a particular value, i.e. if the returned value contains
'16', I want to show "Yes", otherwise "No".

A vlookup might not even be the right function for this, so any help would
be appreciated.

Many thanks in advance.

Identify if the result contains a particular value
 

see if the result of the vlookup contains (not equals)
a particular value, i.e. if the returned value contains
'16', I want to show "Yes", otherwise "No".

Try this:

=IF(COUNT(FIND(16,VLOOKUP($A3,Lookup!$A:$K,9,0))), "Yes","No")


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

VLOOKUP supports wildcards, so if you replace $A3 with

"*"&$A3&"*"

you have "contains"

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sarah (OGI)" wrote:

I've got the following formula:

=IF(VLOOKUP($A3,Lookup!$A:$K,9,FALSE))=16,"Yes","N o")

However, I'd like the formula to now see if the result of the vlookup
contains (not equals) a particular value, i.e. if the returned value
contains
'16', I want to show "Yes", otherwise "No".

A vlookup might not even be the right function for this, so any help
would
be appreciated.

Many thanks in advance.

Identify if the result contains a particular value
 

You've gotten good counsel- Shane's method of doing wildcard matches
will work if the VLookup leftmost column of the lookup table array is
text or numbers stored as text, and if the col index number is 1. I'll
keep that trick in my back pocket...

Biff's use of Count to deal with the fact that FIND will generate an
error if it doesn't find the string is neat - I'd always used the
ISERROR() function to catch that condition, so I'll remember that, too.

One thing no one has mentioned - which may not apply - is to be
cautious if you are dealing with values other than integers -
15.9999999999 will look like 16 in any rationally formatted cell, but
will generate an apparent error because the formula will evaluate to No.
On the other hand, 1783.34916 will evaluate to "yes" when you might not
expect it to. If you are pulling data in from some other source or
depending on user input, the use of the ROUND function might be
appropriate...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111753