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