Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VLOOKUP() can one "fetch" a range of columns?
2003
With VLOOKUP() I know that one can retrieve a R,C data item for the Found row. What is the best way to retrieve a range of columnar data if the Keys match? Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False) If not what is the best approach both with Excel and XL VBA? TIA EagleOne |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VLOOKUP() can one "fetch" a range of columns?
I should have made this point:
If a match, I would like to return all columnar data items on Sheet2 to columns in the same row but different columns on Sheet1 wrote: 2003 With VLOOKUP() I know that one can retrieve a R,C data item for the Found row. What is the best way to retrieve a range of columnar data if the Keys match? Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False) If not what is the best approach both with Excel and XL VBA? TIA EagleOne |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VLOOKUP() can one "fetch" a range of columns?
You really need a vlookup for each cell that will contain data (i.e.: 22
cells in each row will have data or 21 if not including the key, no matter the order, you will need a formula for each cell). -- John C " wrote: I should have made this point: If a match, I would like to return all columnar data items on Sheet2 to columns in the same row but different columns on Sheet1 wrote: 2003 With VLOOKUP() I know that one can retrieve a R,C data item for the Found row. What is the best way to retrieve a range of columnar data if the Keys match? Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False) If not what is the best approach both with Excel and XL VBA? TIA EagleOne |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VLOOKUP() can one "fetch" a range of columns?
I kind of figured that. This is a large w/s and I was attempting to avoid all of the
"Formula-overhead" Thanks John C <johnc@stateofdenial wrote: You really need a vlookup for each cell that will contain data (i.e.: 22 cells in each row will have data or 21 if not including the key, no matter the order, you will need a formula for each cell). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VLOOKUP() can one "fetch" a range of columns?
I think what I'll do is
1) place the "found Sheet2" row-reference in a column on Sheet 2 2) return that value to Sheet1 3) then use VBA to copy/paste the resprctive Row/Cols found-range from Sheet2 to Sheet1 John C <johnc@stateofdenial wrote: You really need a vlookup for each cell that will contain data (i.e.: 22 cells in each row will have data or 21 if not including the key, no matter the order, you will need a formula for each cell). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VLOOKUP() can one "fetch" a range of columns?
Hi EagleOne,
Try this, if I read your post correctly. This is a four column return from the look value, adjust to do 22 columns by adding 6 to 22 in the {}'s inside the formula. You will need to SELECT all the cells you want the returns to be in and while selected, enter the following formula in the selected white cell, all others remain highlighted as you do this. Then array enter, Ctrl + Shift + Enter. This will produce {}'s around the formula, don't enter them yourself. =VLOOKUP(A1,Sheet2!A1:E3,{2,3,4,5},0) If you need to modify the formula later you will need to select all the cells that have the formulas, do your modification and again array enter with Ctrl+Shift+Enter. To delete the formulas, again select all cells and hit Delete and then array enter again. HTH Regards, Howard wrote in message ... 2003 With VLOOKUP() I know that one can retrieve a R,C data item for the Found row. What is the best way to retrieve a range of columnar data if the Keys match? Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False) If not what is the best approach both with Excel and XL VBA? TIA EagleOne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Using "Fetch" function | Excel Worksheet Functions | |||
Possible to "rotate" range of cells so columns are rows and vice versa? | New Users to Excel | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |