Help required. Its a pretty basic if statement but it keeps returning the
wrong answer. I have 2 spreadsheets and I want to look up a column of
numbers in one spreadsheet and return "Priority" if it finds a match and
"Non-Priority" if it doesn't. It keeps returning "Non-Priority" even when I
know it should be returning "Priority". The syntax I have used is
If(A3='[spreadsheet name]sheet name'!$a:$a,"Priority","Non-priority"). What
am I doing wrong????

=IF(COUNTIF('[book name]Sheet name'!$A:$A,A3)0,"Priority","Non-Priority")
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Lou H" wrote:

Help required. Its a pretty basic if statement but it keeps returning the
wrong answer. I have 2 spreadsheets and I want to look up a column of
numbers in one spreadsheet and return "Priority" if it finds a match and
"Non-Priority" if it doesn't. It keeps returning "Non-Priority" even when I
know it should be returning "Priority". The syntax I have used is
If(A3='[spreadsheet name]sheet name'!$a:$a,"Priority","Non-priority"). What
am I doing wrong????

COUNTIF doesn't work on closed files.

Better to use something like:

=IF(ISNUMBER(MATCH(A3,'[spreadsheet name]sheet
name'!$a:$a,0)),"Priority","Non-priority")

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
=IF(COUNTIF('[book name]Sheet name'!$A:$A,A3)0,"Priority","Non-Priority")
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Lou H" wrote:

Help required. Its a pretty basic if statement but it keeps returning
the
wrong answer. I have 2 spreadsheets and I want to look up a column of
numbers in one spreadsheet and return "Priority" if it finds a match and
"Non-Priority" if it doesn't. It keeps returning "Non-Priority" even
when I
know it should be returning "Priority". The syntax I have used is
If(A3='[spreadsheet name]sheet name'!$a:$a,"Priority","Non-priority").
What
am I doing wrong????

Hi,

Of course you might use VLOOKUP

=IF(ISNA(VLOOKUP(A1,'[spreadsheet name]sheet
name'!$A:$A,1,FALSE)),"non-priority","priority")

This works against a closed workbook.

--
Thanks,
Shane Devenshire


"Lou H" wrote:

Help required. Its a pretty basic if statement but it keeps returning the
wrong answer. I have 2 spreadsheets and I want to look up a column of
numbers in one spreadsheet and return "Priority" if it finds a match and
"Non-Priority" if it doesn't. It keeps returning "Non-Priority" even when I
know it should be returning "Priority". The syntax I have used is
If(A3='[spreadsheet name]sheet name'!$a:$a,"Priority","Non-priority"). What
am I doing wrong????