Look In Headers

  • Thread starter Thread starter yossi.frisch
  • Start date Start date
Y

yossi.frisch

HI,

i m looking for a formula, that will look into the headers and return
info from the columns,

A2 = apples

and on sheet2 there is
Apples Pears Oranges

so i will need the of from the row right beneath the word apples, or
the third row. etc.

i tried this http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/779a68ecf831f839
but didn't really work for me, because i got the header value but not
what i m actually looking for

Any help would be great

Joe
 
Hey Joe

did you try the hlookup formula?

Put this in A3 on sheet1
=HLookup(A2,Sheet2!A1:Z100,B2,False)
You may need to change the range.
In B2 you then enter the offset.
1 for the first row after header, 2 for the second row, and so on.

hope that helps

Carlo
 
Hi Joem

<i got the header value but not what i m actually looking for>

So, what *are* you looking for?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| HI,
|
| i m looking for a formula, that will look into the headers and return
| info from the columns,
|
| A2 = apples
|
| and on sheet2 there is
| Apples Pears Oranges
|
| so i will need the of from the row right beneath the word apples, or
| the third row. etc.
|
| i tried this http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/779a68ecf831f839
| but didn't really work for me, because i got the header value but not
| what i m actually looking for
|
| Any help would be great
|
| Joe
 
Thank you Carlo that's exactly what i was looking for,

one other question, could i somehow reference, the horizontal value it
should look for,

for example:

My column header are:
Apples Pears Oranges
my Row labels are
05/08/07 2 5 8
06/07/08 3 6 7
02/04/08 8 8 7

range A2 will say Apples
range B2 will say 06/07/08

and the formula should give me the corresponding value which in this
case is 3

Thanks
Joe

and Niek thank you for replying i hope this answers your question
 
Thank you Carlo that's exactly what i was looking for,

one other question, could i somehow reference, the horizontal value it
should look for,

for example:

My column header are:
                                            Apples Pears Oranges
my Row labels are
                                05/08/07    2          5          8
                                06/07/08    3          6          7
                                02/04/08    8          8          7

range A2 will say Apples
range B2 will say 06/07/08

and the formula should give me the corresponding value which in this
case is 3

Thanks
Joe

and Niek thank you for replying i hope this answers your question
Click to expand...

Hi Joe

If you arrange your data like you did, put this formula in cell B3:
=MATCH(B2,Sheet2!A2:A100,0)
arrange the range to whatever suits you best.

then you can change the formula like that:
=HLookup(A2,Sheet2!A1:Z100,B3,False)

hth

Carlo
 
Back
Top