Hello,

Here is what I want to do:

I have a table of two rows: one row for the months of the year, the second
one with values. Each month has 3 columns, the first one for a text value,
the other ones with number values, looks like that:

row 1onths, m: A1 july B1 july C1 july D1 august E1 august F1 august
row 2, values: A2 rain B2 34 C2 56 D2 sun E2 56 F2 45

this table is let say in a worksheet called table

I want to look up for a given month the first value of the month. Because I
do not want the other value i tried to use an array the following way:

HLOOKUP(lookedupmonth,{table!A1,table!D1;table!A2, table!D2},2,TRUE)

I cannot make it run, it seems that there a problem with the array there

however something like (excel help sample)
HLOOKUP(3;{1,3;"a","b"};2;TRUE)
return b

What is wrong with my array (I put lookeupmonth and first row as text)

Thanks for your help

I want to look up for a given month the first value of the month.

The first value of the month would be the "rain" or the "sun". Or, do you
mean you want the first *numeric value* for the month?

For the "rain" or "sun" :

A5 = July

=INDEX(A2:F2,MATCH(A5,A1:F1,0))

For the first numeric value try this array formula** :

=INDEX(A2:F2,MATCH(1,(A1:F1=A5)*(ISNUMBER(A2:F2)), 0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Francois" wrote in message
...
Hello,

Here is what I want to do:

I have a table of two rows: one row for the months of the year, the second
one with values. Each month has 3 columns, the first one for a text value,
the other ones with number values, looks like that:

row 1onths, m: A1 july B1 july C1 july D1 august E1 august F1 august
row 2, values: A2 rain B2 34 C2 56 D2 sun E2 56 F2 45

this table is let say in a worksheet called table

I want to look up for a given month the first value of the month. Because
I
do not want the other value i tried to use an array the following way:

HLOOKUP(lookedupmonth,{table!A1,table!D1;table!A2, table!D2},2,TRUE)

I cannot make it run, it seems that there a problem with the array there

however something like (excel help sample)
HLOOKUP(3;{1,3;"a","b"};2;TRUE)
return b

What is wrong with my array (I put lookeupmonth and first row as text)

Thanks for your help

I'd try:
=hlookup(lookedupmonth, table!a1:f2, 2,false)

or
=index(2:2,match(lookedupmonth,1:1,0))

The nice thing about the =index(match()) formula is that you could pick off the
2nd and 3rd columns if you wanted:
=index(2:2,match(lookedupmonth,1:1,0)+1)
=index(2:2,match(lookedupmonth,1:1,0)+2)



Francois wrote:

Hello,

Here is what I want to do:

I have a table of two rows: one row for the months of the year, the second
one with values. Each month has 3 columns, the first one for a text value,
the other ones with number values, looks like that:

row 1onths, m: A1 july B1 july C1 july D1 august E1 august F1 august
row 2, values: A2 rain B2 34 C2 56 D2 sun E2 56 F2 45

this table is let say in a worksheet called table

I want to look up for a given month the first value of the month. Because I
do not want the other value i tried to use an array the following way:

HLOOKUP(lookedupmonth,{table!A1,table!D1;table!A2, table!D2},2,TRUE)

I cannot make it run, it seems that there a problem with the array there

however something like (excel help sample)
HLOOKUP(3;{1,3;"a","b"};2;TRUE)
return b

What is wrong with my array (I put lookeupmonth and first row as text)

Thanks for your help

--

Dave Peterson