Vlookup returning a YES or NO
 

Hello

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

Vlookup returning a YES or NO
 

Hi,

Just do not understand what you are trying to do:

Why is there no column reference in your range_lookup I.e. how can it be
$1:$5000.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rob" wrote in message
...
Hello

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

Vlookup returning a YES or NO
 

hi, Rob !

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

AFAIK, vlookup will try to find your lookup_value in the first column of your lookup_table
so, it doesn't make sense to incude whole columns by reference of the entire rows -?-

try with:

=if(countif('[Catalogue Robs Test Stock Range.xls]Catalogue'!$A$1:$A$5000,A6),"YES,"NO")

hth,
hector.

Vlookup returning a YES or NO
 

I must have the found the most complicated way to half do it.
Thanks Miguel that works a treat.

"Héctor Miguel" wrote:

hi, Rob !

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

AFAIK, vlookup will try to find your lookup_value in the first column of your lookup_table
so, it doesn't make sense to incude whole columns by reference of the entire rows -?-

try with:

=if(countif('[Catalogue Robs Test Stock Range.xls]Catalogue'!$A$1:$A$5000,A6),"YES,"NO")

hth,
hector.

Vlookup returning a YES or NO
 

Note that COUNTIF requires the source file be open. An alternative that
works whether the source file is open or closed:

=IF(COUNT(MATCH(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$A$1:$A$5000,0)),"YES,"NO")


--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I must have the found the most complicated way to half do it.
Thanks Miguel that works a treat.

"Héctor Miguel" wrote:

hi, Rob !

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

AFAIK, vlookup will try to find your lookup_value in the first column of
your lookup_table
so, it doesn't make sense to incude whole columns by reference of the
entire rows -?-

try with:

=if(countif('[Catalogue Robs Test Stock
Range.xls]Catalogue'!$A$1:$A$5000,A6),"YES,"NO")

hth,
hector.

Vlookup returning a YES or NO
 

Wouldn't you need the full path if the file is closed?

Pete

On Sep 17, 7:06*pm, "T. Valko" wrote:
Note that COUNTIF requires the source file be open. An alternative that
works whether the source file is open or closed:

=IF(COUNT(MATCH(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$A$1:$A$5000,0)),"YES,"NO")

--
Biff
Microsoft Excel MVP

Vlookup returning a YES or NO
 

hi, all !

Pete_UK wrote in message ...
Wouldn't you need the full path if the file is closed?

since OP posted a formula without the full-path, I assumed he's working (only ?) with source file opened
as soon as the source file is closed, each reference in formulae change to it's full-path (but)...
as Tony says, sumif(... countif(... doesn't work on closed files, so forth his proposal was...

regards,
hector.

T. Valko wrote in message ...
Note that COUNTIF requires the source file be open.
An alternative that works whether the source file is open or closed:
=IF(COUNT(MATCH(A6,'[Catalogue Robs Test Stock Range.xls]Catalogue'!$A$1:$A$5000,0)),"YES,"NO")