Thank you a lot!
This really helped. I got error result at the beginning using this
advice because I have different locale settings and instead of 'FALSE' I
have to write 'APLAMS'. Is there also solution for random values in
table to chose variable directly on the base of values in the table
header and not a number of row?
Sincerely,
Andis
Brian Barker wrote:
At 23:04 28/09/2008 +0300, Andis Noname wrote:
I'm looking if there is easy possibility to chose variable from the
table, like in example below, to use in calculations is another table.
I have table like this, but much longer, to chose variables from:
*Average height (m)* *Aspen* *Grey alder* *Birch*
1 2000 2000 2000
2 1800 1800 1800
3 1600 1600 1600
4 1600 1600 1600
5 1600 1600 1600
6 1600 1600 1600
7 1500 1600 1500
8 1400 1500 1400
9 1300 1500 1300
10 1200 1500 1200
11 1100 1300 1100
And I have another table with the results of forest inventory, which
contains columns specie and average height, besides other 245
variables. I need additional column with some kind of lookup field,
where according to a specie and average tree height I can see
regulatory numbers of trees (values in the first table). For instance
if the main specie is Aspen and average height of trees is 10 m,
regulatory number of trees should be 1200.
What are the options to do it, preferably using array functions?
I don't think you need an array function here, in fact. Your problem
is made easier by the fact that the first column is a regular
sequence, not random values. You can use the HLOOKUP() function to
search the column headers for the correct species and then use the
average height directly to choose the row from which the result should
be taken. Suppose that your data table in is cells A1 to D12 and that
the species, average height, and your required number in the other
table are in columns G, H, and I. In I1, put:
=HLOOKUP(G1;B$1:D$12;H1+1;FALSE)
Here, B1:D12 is the array of values in your table, G1 is the species
value you are looking for - Aspen in your example - and H1 is the
average height value. The function searches the first row - the
column headers - for your required species, and then returns the value
in the specified row of that column. Since your average heights have
1 in row 2, 2 in row 3, and so on, you need H1+1 to specify the
required row: 10 in H1 gives 11 as the required row. The final
"FALSE" is necessary to indicate that the column headers are not
sorted. The two $ signs are not necessary for the formula to work,
but will be - to prevent the table reference changing - if you wish to
copy the formula down the relevant column (I). A good alternative is
to give your data table a name and reference it by that rather than
actual cell identifiers.
Oh, and I found that column names with asterisks confused the
function, so you'd be better off without these.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
