vlookup-after the value is returned-return a value depending on which one.

i want to find a stock code in another workbook in any one of three
worksheets. i want to find the code in column B and have the rating value in
column D returned as the value in my current workbook.
=VLOOKUP'[copy2ShareRecommendations.xls]('A4',B10:B320,valueof)
so copy2... is the workbook i am searching (i want to search the 1st 3
worksheets), i want to find whatever is in cell A4 in the current sheet,
searching the range of column D, and return the value found in Col D

after the value is returned from one of the three worksheets, i want another
column in my current sheet to return which sheet from the other workbook it
came from and return a value depending on which one.
phew!

thanks so much anyone

i have this formula for vlookup but i get an error for returning the 3rd
column value in copy2share workbook/
=VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
Infrastructure'!B10:B320,'[copy 2Share Recommendations.xls]Stocks:Property &
Infrastructure'!B10:B320, 3)

anyone??

"Micayla Bergen" wrote:

> i want to find a stock code in another workbook in any one of three
> worksheets. i want to find the code in column B and have the rating value in
> column D returned as the value in my current workbook.
> =VLOOKUP'[copy2ShareRecommendations.xls]('A4',B10:B320,valueof)
> so copy2... is the workbook i am searching (i want to search the 1st 3
> worksheets), i want to find whatever is in cell A4 in the current sheet,
> searching the range of column D, and return the value found in Col D
>
> after the value is returned from one of the three worksheets, i want another
> column in my current sheet to return which sheet from the other workbook it
> came from and return a value depending on which one.
> phew!
>
> thanks so much anyone

"Micayla Bergen" <[email protected]>ha scritto nel
messaggio [email protected]

> i have this formula for vlookup but i get an error for returning the
> 3rd column value in copy2share workbook/
> =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> Infrastructure'!B10:B320,'[copy 2Share
> Recommendations.xls]Stocks:Property & Infrastructure'!B10:B320, 3)


Which kind of error? Maybe you forget to add FALSE as the fourth argument in
the function; try this:

=VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
Infrastructure'!B10:B320,'[copy 2Share
Recommendations.xls]Stocks:Property & Infrastructure'!B10:B320, 3,FALSE)

--
Hoping to be helpful...

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

=vlookup() will use a table on a single sheet--not all the sheets between Stocks
and Properties.

Maybe something like:

=VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks'!B10:d320, 3, false)

Note that the range is usually more than one column, too. And use false as the
fourth argument if you want the match to be exact.

Micayla Bergen wrote:
>
> i have this formula for vlookup but i get an error for returning the 3rd
> column value in copy2share workbook/
> =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> Infrastructure'!B10:B320,'[copy 2Share Recommendations.xls]Stocks:Property &
> Infrastructure'!B10:B320, 3)
>
> anyone??
>
> "Micayla Bergen" wrote:
>
> > i want to find a stock code in another workbook in any one of three
> > worksheets. i want to find the code in column B and have the rating value in
> > column D returned as the value in my current workbook.
> > =VLOOKUP'[copy2ShareRecommendations.xls]('A4',B10:B320,valueof)
> > so copy2... is the workbook i am searching (i want to search the 1st 3
> > worksheets), i want to find whatever is in cell A4 in the current sheet,
> > searching the range of column D, and return the value found in Col D
> >
> > after the value is returned from one of the three worksheets, i want another
> > column in my current sheet to return which sheet from the other workbook it
> > came from and return a value depending on which one.
> > phew!
> >
> > thanks so much anyone

--

Dave Peterson

i have changed my formula slightly as suggested, but the error remains and it
says that the "'[copy" section in the col_index_num is the problem. i want
the returned value to be the corresponding cell in the third column and that
is part of the name of the file to find the 3rd column. not sure if that
makes sense, confusing myself really.
Any further suggestions?
Many Thanks

"Franz" wrote:

> "Micayla Bergen" <[email protected]>ha scritto nel
> messaggio [email protected]
>
> > i have this formula for vlookup but i get an error for returning the
> > 3rd column value in copy2share workbook/
> > =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> > Infrastructure'!B10:B320,'[copy 2Share
> > Recommendations.xls]Stocks:Property & Infrastructure'!B10:B320, 3)
>
>
> Which kind of error? Maybe you forget to add FALSE as the fourth argument in
> the function; try this:
>
> =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> Infrastructure'!B10:B320,'[copy 2Share
> Recommendations.xls]Stocks:Property & Infrastructure'!B10:B320, 3,FALSE)
>
> --
> Hoping to be helpful...
>
> Regards
>
> Franz
>
> ----------------------------------------------------------------------------------------
> To reply translate from italian InVento (no capital letters)
> ----------------------------------------------------------------------------------------
>
>
>

Can i extend the formula to look up all 3 at once but reword to what i
currently have? i.e.=VLOOKUP(A4,'[copy 2Share
Recommendations.xls]Stocks'!B10:D320, 3, FALSE) then the Hybrids sheet then
the Property sheet?

"Dave Peterson" wrote:

> =vlookup() will use a table on a single sheet--not all the sheets between Stocks
> and Properties.
>
> Maybe something like:
>
> =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks'!B10:d320, 3, false)
>
> Note that the range is usually more than one column, too. And use false as the
> fourth argument if you want the match to be exact.
>
> Micayla Bergen wrote:
> >
> > i have this formula for vlookup but i get an error for returning the 3rd
> > column value in copy2share workbook/
> > =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> > Infrastructure'!B10:B320,'[copy 2Share Recommendations.xls]Stocks:Property &
> > Infrastructure'!B10:B320, 3)
> >
> > anyone??
> >
> > "Micayla Bergen" wrote:
> >
> > > i want to find a stock code in another workbook in any one of three
> > > worksheets. i want to find the code in column B and have the rating value in
> > > column D returned as the value in my current workbook.
> > > =VLOOKUP'[copy2ShareRecommendations.xls]('A4',B10:B320,valueof)
> > > so copy2... is the workbook i am searching (i want to search the 1st 3
> > > worksheets), i want to find whatever is in cell A4 in the current sheet,
> > > searching the range of column D, and return the value found in Col D
> > >
> > > after the value is returned from one of the three worksheets, i want another
> > > column in my current sheet to return which sheet from the other workbook it
> > > came from and return a value depending on which one.
> > > phew!
> > >
> > > thanks so much anyone
>
> --
>
> Dave Peterson
>

You can nest the =vlookup()'s so that if one returns an error, you advance to
the next, ...

=IF(NOT(ISERROR(VLOOKUP(A4,Sheet1!B10:D320,3,FALSE))),
VLOOKUP(A4,Sheet1!B10:D320,3,FALSE),
IF(NOT(ISERROR(VLOOKUP(A4,Sheet2!B10:D320,3,FALSE))),
VLOOKUP(A4,Sheet2!B10:D320,3,FALSE),
IF(NOT(ISERROR(VLOOKUP(A4,Sheet3!B10:D320,3,FALSE))),
VLOOKUP(A4,Sheet3!B10:D320,3,FALSE),
"missing from all")))

(All one cell. There's a limit to the number of characters you can put into a
formula in excel. It's 1024 (in R1C1 reference style).)

I would build the formula with that other workbook open. Then when you close
the other workbook, you'll see that the path is included in the formula, too.

You may want to just put a formula to return the value from each sheet in it's
own cell. Then use a formula to pick out the value you want:

=if(not(iserror(d2)),d2,if(not(iserror(e2)),e2,if(not(iserror(f2)),f2,
"all errors")

(all one cell)



Micayla Bergen wrote:
>
> Can i extend the formula to look up all 3 at once but reword to what i
> currently have? i.e.=VLOOKUP(A4,'[copy 2Share
> Recommendations.xls]Stocks'!B10:D320, 3, FALSE) then the Hybrids sheet then
> the Property sheet?
>
> "Dave Peterson" wrote:
>
> > =vlookup() will use a table on a single sheet--not all the sheets between Stocks
> > and Properties.
> >
> > Maybe something like:
> >
> > =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks'!B10:d320, 3, false)
> >
> > Note that the range is usually more than one column, too. And use false as the
> > fourth argument if you want the match to be exact.
> >
> > Micayla Bergen wrote:
> > >
> > > i have this formula for vlookup but i get an error for returning the 3rd
> > > column value in copy2share workbook/
> > > =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> > > Infrastructure'!B10:B320,'[copy 2Share Recommendations.xls]Stocks:Property &
> > > Infrastructure'!B10:B320, 3)
> > >
> > > anyone??
> > >
> > > "Micayla Bergen" wrote:
> > >
> > > > i want to find a stock code in another workbook in any one of three
> > > > worksheets. i want to find the code in column B and have the rating value in
> > > > column D returned as the value in my current workbook.
> > > > =VLOOKUP'[copy2ShareRecommendations.xls]('A4',B10:B320,valueof)
> > > > so copy2... is the workbook i am searching (i want to search the 1st 3
> > > > worksheets), i want to find whatever is in cell A4 in the current sheet,
> > > > searching the range of column D, and return the value found in Col D
> > > >
> > > > after the value is returned from one of the three worksheets, i want another
> > > > column in my current sheet to return which sheet from the other workbook it
> > > > came from and return a value depending on which one.
> > > > phew!
> > > >
> > > > thanks so much anyone
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

This worked but i get a reference error when i have anything other than 1 as
the col_index_num??????????

"Dave Peterson" wrote:

> =vlookup() will use a table on a single sheet--not all the sheets between Stocks
> and Properties.
>
> Maybe something like:
>
> =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks'!B10:d320, 3, false)
>
> Note that the range is usually more than one column, too. And use false as the
> fourth argument if you want the match to be exact.
>
> Micayla Bergen wrote:
> >
> > i have this formula for vlookup but i get an error for returning the 3rd
> > column value in copy2share workbook/
> > =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> > Infrastructure'!B10:B320,'[copy 2Share Recommendations.xls]Stocks:Property &
> > Infrastructure'!B10:B320, 3)
> >
> > anyone??
> >
> > "Micayla Bergen" wrote:
> >
> > > i want to find a stock code in another workbook in any one of three
> > > worksheets. i want to find the code in column B and have the rating value in
> > > column D returned as the value in my current workbook.
> > > =VLOOKUP'[copy2ShareRecommendations.xls]('A4',B10:B320,valueof)
> > > so copy2... is the workbook i am searching (i want to search the 1st 3
> > > worksheets), i want to find whatever is in cell A4 in the current sheet,
> > > searching the range of column D, and return the value found in Col D
> > >
> > > after the value is returned from one of the three worksheets, i want another
> > > column in my current sheet to return which sheet from the other workbook it
> > > came from and return a value depending on which one.
> > > phew!
> > >
> > > thanks so much anyone
>
> --
>
> Dave Peterson
>

From xl2003's help:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

This is a guess....

I'm betting that your table_array has fewer columns in it than the number in
col_index_num.

For instance:

=vlookup(a4,sheet2!a:d,6,false)
will return an error. I'm trying to bring back column 6 of a 4 column range (A
to D).



Micayla Bergen wrote:
>
> This worked but i get a reference error when i have anything other than 1 as
> the col_index_num??????????
>
> "Dave Peterson" wrote:
>
> > =vlookup() will use a table on a single sheet--not all the sheets between Stocks
> > and Properties.
> >
> > Maybe something like:
> >
> > =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks'!B10:d320, 3, false)
> >
> > Note that the range is usually more than one column, too. And use false as the
> > fourth argument if you want the match to be exact.
> >
> > Micayla Bergen wrote:
> > >
> > > i have this formula for vlookup but i get an error for returning the 3rd
> > > column value in copy2share workbook/
> > > =VLOOKUP(A4,'[copy 2Share Recommendations.xls]Stocks:Property &
> > > Infrastructure'!B10:B320,'[copy 2Share Recommendations.xls]Stocks:Property &
> > > Infrastructure'!B10:B320, 3)
> > >
> > > anyone??
> > >
> > > "Micayla Bergen" wrote:
> > >
> > > > i want to find a stock code in another workbook in any one of three
> > > > worksheets. i want to find the code in column B and have the rating value in
> > > > column D returned as the value in my current workbook.
> > > > =VLOOKUP'[copy2ShareRecommendations.xls]('A4',B10:B320,valueof)
> > > > so copy2... is the workbook i am searching (i want to search the 1st 3
> > > > worksheets), i want to find whatever is in cell A4 in the current sheet,
> > > > searching the range of column D, and return the value found in Col D
> > > >
> > > > after the value is returned from one of the three worksheets, i want another
> > > > column in my current sheet to return which sheet from the other workbook it
> > > > came from and return a value depending on which one.
> > > > phew!
> > > >
> > > > thanks so much anyone
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson