Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Return all figures in a Range
G’Day
I am looking for a way to create a “Look up” that will return more then one figure. I have a table that has 400 rows and 50 columns and I would like my look up to return the entire line of 50 figures. I currently use 50 individual Vlookup’s The Vlookup parameters change according to what the user wants to see via concatenated cells. Thanking you, Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return all figures in a Range
"michaelp" wrote in message ... G’Day I am looking for a way to create a “Look up” that will return more then one figure. I have a table that has 400 rows and 50 columns and I would like my look up to return the entire line of 50 figures. I currently use 50 individual Vlookup’s The Vlookup parameters change according to what the user wants to see via concatenated cells. It's possible. You probable now use formulas that look like =VLOOKUP(A1,A100:A500,2) , =VLOOKUP(A1,A100:A500,3) etc. Instead of writing formulas, each containing one column no., you could write the column numbers as an array. The formula would look like =VLOOKUP(A1,A100:A500,{2,3,4,5,6 ...49,50}). In order to make this formula work, you have to input is as an array formula: 1) Select the range that should contain the 49 figures (above example assumes that the user provides the first value in A1, so select B1:AX1) 2) Input the formula 3) Instead of ENTER, use CTRL+SHIFT+ENTER. This will put curly braces aroung the formula, indicating an array formula. Cheers Joerg |
#3
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT | Excel Worksheet Functions | |||
Sumif to return a blank if sum range is blank | Excel Worksheet Functions | |||
return based on range of dates | Excel Worksheet Functions | |||
calculating return in a range | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions |