vlookup

M

Micayla Bergen

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
 
M

Micayla Bergen

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??
 
F

Franz

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)
Click to expand...


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
 
D

Dave Peterson

=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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Micayla Bergen

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 said:
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)
Click to expand...


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
Click to expand...
 
M

Micayla Bergen

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 said:
=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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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 said:
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 said:
=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 said:
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??

:

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
Click to expand...
Click to expand...
Click to expand...
 
M

Micayla Bergen

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

Dave Peterson said:
=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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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 said:
This worked but i get a reference error when i have anything other than 1 as
the col_index_num??????????

Dave Peterson said:
=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 said:
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??

:

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
Click to expand...
Click to expand...
Click to expand...
 
Top