Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
|
#4
|
|||
|
|||
=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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
From xl2003's help:
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |