i have this formula in 3 columns but w different tabs in the spreadsheet
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share Recommendations.xls]Stocks'!$B$10:$D$413,2),1,"")
i.e. a column looking in the "shares" tab then "stocks" then "model",
however they all return a value of 1 even though A5 will only be found in one
of them.
i am not sure what i have done wrong.

thanks anyone

You MAY want to check the returned value of the VLOOKUP, maybe like:
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2)=A5,1,"")
(I added "=A5" to the formula).
Your formula is testing if the VLOOKUP is anything but False. Since it
isn't, it returns a 1.
HTH
Bob Umlas
Excel MVP

"Micayla Bergen" wrote in message
...
i have this formula in 3 columns but w different tabs in the spreadsheet
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2),1,"")
i.e. a column looking in the "shares" tab then "stocks" then "model",
however they all return a value of 1 even though A5 will only be found in
one
of them.
i am not sure what i have done wrong.

thanks anyone

Hi Bob
i added =A5 as you suggested but now all the cells are empty, even though A5
is in the "stocks" sheet.
ideally what i want is for the formula to check for A5 in the 3 sheets and
return a 1 if its there, but i know if i use a vlookup it has to return a
value from the same sheet, so i have a column for each tab and have isolated
the function to only check one tab each, so then i can tally them later. i
though this would be an easy way. do you have any other suggestions?

"Bob Umlas" wrote:

You MAY want to check the returned value of the VLOOKUP, maybe like:
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2)=A5,1,"")
(I added "=A5" to the formula).
Your formula is testing if the VLOOKUP is anything but False. Since it
isn't, it returns a 1.
HTH
Bob Umlas
Excel MVP

"Micayla Bergen" wrote in message
...
i have this formula in 3 columns but w different tabs in the spreadsheet
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2),1,"")
i.e. a column looking in the "shares" tab then "stocks" then "model",
however they all return a value of 1 even though A5 will only be found in
one
of them.
i am not sure what i have done wrong.

thanks anyone

Try this:

=IF(ISNA(MATCH(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$B$413,0)),"",1)

If the path and range of that other file are static you could use a defined
name to refer to it then use a much cleaner looking formula like:

=IF(ISNA(MATCH(A5,path,0)),"",1)

Biff

"Micayla Bergen" wrote in message
...
Hi Bob
i added =A5 as you suggested but now all the cells are empty, even though
A5
is in the "stocks" sheet.
ideally what i want is for the formula to check for A5 in the 3 sheets and
return a 1 if its there, but i know if i use a vlookup it has to return a
value from the same sheet, so i have a column for each tab and have
isolated
the function to only check one tab each, so then i can tally them later. i
though this would be an easy way. do you have any other suggestions?

"Bob Umlas" wrote:

You MAY want to check the returned value of the VLOOKUP, maybe like:
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2)=A5,1,"")
(I added "=A5" to the formula).
Your formula is testing if the VLOOKUP is anything but False. Since it
isn't, it returns a 1.
HTH
Bob Umlas
Excel MVP

"Micayla Bergen" wrote in
message
...
i have this formula in 3 columns but w different tabs in the spreadsheet
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2),1,"")
i.e. a column looking in the "shares" tab then "stocks" then "model",
however they all return a value of 1 even though A5 will only be found
in
one
of them.
i am not sure what i have done wrong.

thanks anyone

Hi Biff
the bottom one works best

thank you!

"Biff" wrote:

Try this:

=IF(ISNA(MATCH(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$B$413,0)),"",1)

If the path and range of that other file are static you could use a defined
name to refer to it then use a much cleaner looking formula like:

=IF(ISNA(MATCH(A5,path,0)),"",1)

Biff

"Micayla Bergen" wrote in message
...
Hi Bob
i added =A5 as you suggested but now all the cells are empty, even though
A5
is in the "stocks" sheet.
ideally what i want is for the formula to check for A5 in the 3 sheets and
return a 1 if its there, but i know if i use a vlookup it has to return a
value from the same sheet, so i have a column for each tab and have
isolated
the function to only check one tab each, so then i can tally them later. i
though this would be an easy way. do you have any other suggestions?

"Bob Umlas" wrote:

You MAY want to check the returned value of the VLOOKUP, maybe like:
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2)=A5,1,"")
(I added "=A5" to the formula).
Your formula is testing if the VLOOKUP is anything but False. Since it
isn't, it returns a 1.
HTH
Bob Umlas
Excel MVP

"Micayla Bergen" wrote in
message
...
i have this formula in 3 columns but w different tabs in the spreadsheet
=IF(VLOOKUP(A5,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,2),1,"")
i.e. a column looking in the "shares" tab then "stocks" then "model",
however they all return a value of 1 even though A5 will only be found
in
one
of them.
i am not sure what i have done wrong.

thanks anyone