vlookup 3 columns all return same

[Micayla Bergen]

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

[Bob Umlas]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

[Micayla Bergen]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
>
>
>

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >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
>> >
>>
>>
>>

[Micayla Bergen]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>