looking a value in an array text values
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
|