IF vlookup return "value"

[Micayla Bergen]

i want the cell to check 3 worksheets from another workbook and return S, FI
or PI depending on which sheet it is on, have the following but know its
wrong, cannot fine tune. thanks anyone

=IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2Share
Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My Documents\MDA\[copy
2Share Recommendations.xls]Property & Infrastructure'!$B$10:$D$413,PI,))))))

[Biff]

Hi!

Yuck!

Maybe something like this:

=IF(COUNTIF('Path\[copy 2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
2share recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))

Replace "Path" with:

'C:\Documents and Settings\Gillian Mason\My Documents\MDA

Biff

"Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message
news:2AA742B7-0631-40AC-A116-69844FFA18FB@microsoft.com...
>i want the cell to check 3 worksheets from another workbook and return S,
>FI
> or PI depending on which sheet it is on, have the following but know its
> wrong, cannot fine tune. thanks anyone
>
> =IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> Documents\MDA\[copy 2Share
> Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents and
> Settings\Gillian Mason\My Documents\MDA\[copy 2Share
> Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
> IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> Documents\MDA\[copy
> 2Share Recommendations.xls]Property &
> Infrastructure'!$B$10:$D$413,PI,))))))

[Micayla Bergen]

Thanks Biff. now it seems to work in that it doesnt ask for more info, but it
is blank when i know the value in A4 is on the first worksheet of the other
doc.
what say you

=IF(COUNTIF('‘C:\Documents and Settings\Gillian Mason\My Documents\MDA\[copy
2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('‘C:\Documents
and Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('‘C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Property & Infrastructure'!$B$10:$B$413,A4),"PI","None")))


"Biff" wrote:

> Hi!
>
> Yuck!
>
> Maybe something like this:
>
> =IF(COUNTIF('Path\[copy 2share
> recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
> 2share recommendations.xls]Income
> securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
> recommendations.xls]Property &
> Infrastructure'!$B$10:$B$413,A4),"PI","None")))
>
> Replace "Path" with:
>
> 'C:\Documents and Settings\Gillian Mason\My Documents\MDA
>
> Biff
>
> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message
> news:2AA742B7-0631-40AC-A116-69844FFA18FB@microsoft.com...
> >i want the cell to check 3 worksheets from another workbook and return S,
> >FI
> > or PI depending on which sheet it is on, have the following but know its
> > wrong, cannot fine tune. thanks anyone
> >
> > =IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> > Documents\MDA\[copy 2Share
> > Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents and
> > Settings\Gillian Mason\My Documents\MDA\[copy 2Share
> > Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
> > IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> > Documents\MDA\[copy
> > 2Share Recommendations.xls]Property &
> > Infrastructure'!$B$10:$D$413,PI,))))))
>
>
>

[Biff]

Hi!

What do you mean by: "it doesnt ask for more info" ?

>it is blank when i know the value in A4

What is blank? The result of the formula? It can't be. The only possible
values that it can return are: S, FI, PI or NONE (or an error if you have
errors in any of those ranges).

Biff

"Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message
news:D06BE318-2852-4BB5-89E7-8C2DBAFED4FA@microsoft.com...
> Thanks Biff. now it seems to work in that it doesnt ask for more info, but
> it
> is blank when i know the value in A4 is on the first worksheet of the
> other
> doc.
> what say you
>
> =IF(COUNTIF(''C:\Documents and Settings\Gillian Mason\My
> Documents\MDA\[copy
> 2share
> recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF(''C:\Documents
> and Settings\Gillian Mason\My Documents\MDA\[copy 2share
> recommendations.xls]Income
> securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF(''C:\Documents and
> Settings\Gillian Mason\My Documents\MDA\[copy 2share
> recommendations.xls]Property &
> Infrastructure'!$B$10:$B$413,A4),"PI","None")))
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Yuck!
>>
>> Maybe something like this:
>>
>> =IF(COUNTIF('Path\[copy 2share
>> recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
>> 2share recommendations.xls]Income
>> securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
>> recommendations.xls]Property &
>> Infrastructure'!$B$10:$B$413,A4),"PI","None")))
>>
>> Replace "Path" with:
>>
>> 'C:\Documents and Settings\Gillian Mason\My Documents\MDA
>>
>> Biff
>>
>> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in
>> message
>> news:2AA742B7-0631-40AC-A116-69844FFA18FB@microsoft.com...
>> >i want the cell to check 3 worksheets from another workbook and return
>> >S,
>> >FI
>> > or PI depending on which sheet it is on, have the following but know
>> > its
>> > wrong, cannot fine tune. thanks anyone
>> >
>> > =IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
>> > Documents\MDA\[copy 2Share
>> > Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents
>> > and
>> > Settings\Gillian Mason\My Documents\MDA\[copy 2Share
>> > Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
>> > IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
>> > Documents\MDA\[copy
>> > 2Share Recommendations.xls]Property &
>> > Infrastructure'!$B$10:$D$413,PI,))))))
>>
>>
>>

[Micayla Bergen]

thats exactly right! it returns nothing, whereas it should return something.
what i mean by ask for more info is if the formula was incomplete or
incorrect it usually has a dialogue box to that effect.

"Biff" wrote:

> Hi!
>
> What do you mean by: "it doesnt ask for more info" ?
>
> >it is blank when i know the value in A4
>
> What is blank? The result of the formula? It can't be. The only possible
> values that it can return are: S, FI, PI or NONE (or an error if you have
> errors in any of those ranges).
>
> Biff
>
> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message
> news:D06BE318-2852-4BB5-89E7-8C2DBAFED4FA@microsoft.com...
> > Thanks Biff. now it seems to work in that it doesnt ask for more info, but
> > it
> > is blank when i know the value in A4 is on the first worksheet of the
> > other
> > doc.
> > what say you
> >
> > =IF(COUNTIF(''C:\Documents and Settings\Gillian Mason\My
> > Documents\MDA\[copy
> > 2share
> > recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF(''C:\Documents
> > and Settings\Gillian Mason\My Documents\MDA\[copy 2share
> > recommendations.xls]Income
> > securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF(''C:\Documents and
> > Settings\Gillian Mason\My Documents\MDA\[copy 2share
> > recommendations.xls]Property &
> > Infrastructure'!$B$10:$B$413,A4),"PI","None")))
> >
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> Yuck!
> >>
> >> Maybe something like this:
> >>
> >> =IF(COUNTIF('Path\[copy 2share
> >> recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
> >> 2share recommendations.xls]Income
> >> securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
> >> recommendations.xls]Property &
> >> Infrastructure'!$B$10:$B$413,A4),"PI","None")))
> >>
> >> Replace "Path" with:
> >>
> >> 'C:\Documents and Settings\Gillian Mason\My Documents\MDA
> >>
> >> Biff
> >>
> >> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in
> >> message
> >> news:2AA742B7-0631-40AC-A116-69844FFA18FB@microsoft.com...
> >> >i want the cell to check 3 worksheets from another workbook and return
> >> >S,
> >> >FI
> >> > or PI depending on which sheet it is on, have the following but know
> >> > its
> >> > wrong, cannot fine tune. thanks anyone
> >> >
> >> > =IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> >> > Documents\MDA\[copy 2Share
> >> > Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents
> >> > and
> >> > Settings\Gillian Mason\My Documents\MDA\[copy 2Share
> >> > Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
> >> > IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> >> > Documents\MDA\[copy
> >> > 2Share Recommendations.xls]Property &
> >> > Infrastructure'!$B$10:$D$413,PI,))))))
> >>
> >>
> >>
>
>
>

[Micayla Bergen]

sorry, i had my text in white. it is returning a value error.

"Biff" wrote:

> Hi!
>
> What do you mean by: "it doesnt ask for more info" ?
>
> >it is blank when i know the value in A4
>
> What is blank? The result of the formula? It can't be. The only possible
> values that it can return are: S, FI, PI or NONE (or an error if you have
> errors in any of those ranges).
>
> Biff
>
> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message
> news:D06BE318-2852-4BB5-89E7-8C2DBAFED4FA@microsoft.com...
> > Thanks Biff. now it seems to work in that it doesnt ask for more info, but
> > it
> > is blank when i know the value in A4 is on the first worksheet of the
> > other
> > doc.
> > what say you
> >
> > =IF(COUNTIF(''C:\Documents and Settings\Gillian Mason\My
> > Documents\MDA\[copy
> > 2share
> > recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF(''C:\Documents
> > and Settings\Gillian Mason\My Documents\MDA\[copy 2share
> > recommendations.xls]Income
> > securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF(''C:\Documents and
> > Settings\Gillian Mason\My Documents\MDA\[copy 2share
> > recommendations.xls]Property &
> > Infrastructure'!$B$10:$B$413,A4),"PI","None")))
> >
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> Yuck!
> >>
> >> Maybe something like this:
> >>
> >> =IF(COUNTIF('Path\[copy 2share
> >> recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
> >> 2share recommendations.xls]Income
> >> securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
> >> recommendations.xls]Property &
> >> Infrastructure'!$B$10:$B$413,A4),"PI","None")))
> >>
> >> Replace "Path" with:
> >>
> >> 'C:\Documents and Settings\Gillian Mason\My Documents\MDA
> >>
> >> Biff
> >>
> >> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in
> >> message
> >> news:2AA742B7-0631-40AC-A116-69844FFA18FB@microsoft.com...
> >> >i want the cell to check 3 worksheets from another workbook and return
> >> >S,
> >> >FI
> >> > or PI depending on which sheet it is on, have the following but know
> >> > its
> >> > wrong, cannot fine tune. thanks anyone
> >> >
> >> > =IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> >> > Documents\MDA\[copy 2Share
> >> > Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents
> >> > and
> >> > Settings\Gillian Mason\My Documents\MDA\[copy 2Share
> >> > Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
> >> > IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
> >> > Documents\MDA\[copy
> >> > 2Share Recommendations.xls]Property &
> >> > Infrastructure'!$B$10:$D$413,PI,))))))
> >>
> >>
> >>
>
>
>