Help With HLOOK

[Hummer2097]

I have a spreadsheet for calculating the pivot points on stocks,
screenshot of which can be seen 'here
(http://img237.imageshack.us/img237/5785/pivots4cq.gif)

The problem with it is I have to scroll side to side to find the stoc
I’m looking for, which is a little cumbersome. I’ve added an extr
empty box at the start so I can type in the ticker for the stock I wan
in E2. I’d then like it to search along row number 2 and find th
ticker that matches the one I just type in. I’d then like it t
populate the cells D21:F25 with the equivalent cells for the matchin
ticker e.g. L21:L25 for AMZN.

It seemed simple enough and HLOOKUP looked to be the way to go bu
after some considerable time I’ve been unable to do it. It appear
impossible to fill in the D2125 and F21:F25 columns with HLOOKU
though E21:E25 looks possible. The only other way I could think was
massive nested IF statement but then I found out you can only nest t
seven layers. I couldn’t find anything like a switch case so I’
stumped and can only think to start looking into VBA.

Is HLOOKUP the appropriate function and if so what should I be puttin
in the D21:F25 cells? If not what should I be using?

Sorry, this is probably a really stupid question but I can’t ge
anything to work. Thanks for any help you can ofer

 

[Don Guillett]

Lots of ways to do this. MATCH to find the column for your variable and then
offset formulas. I would probably use a worksheet_change event macro to do
it instead using FIND and offset.

 

[Biff]

Hi!

Select the range D21:F25

Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(E2="","",OFFSET(A2,19,MATCH(E2,F2:IV2,0)+3,5,3))

Biff

 

[Biff]

P.S.

Can't really tell from the screencap but I hope the 3 cells that hold the
ticker symbol *AREN"T* merged cells. If so, that formula won't work.

Biff

 

[Hummer2097]

Thanks a lot for the reply. I just used OFFSET and MATCH like you sai
and put a formulae in each of the 15 boxs like this with a diffen
value for rows and colums:

=OFFSET($H$2, 19, MATCH($E$2,$H$2:$FF$2, 0), 1, 1)

With your help it just took me two minutes to do what I’d spent over a
hour on.

Just saw your reply as well Biff. Thanks for that. I couldn't seem t
get it working with the 5,3 and had to have 15 different 1,1 formual
but it looks like it's becasue I wasn't doing the Ctrl+Shift+Enter.
I've changed it to your formula now and it works great.

Thanks a lot for the help

 

[Biff]

Hi!

You need to select the entire block of cells as an array. It's a single
formula that returns an array of values.

Glad you got it working!

Biff

 

[Hummer2097]

I was selecting the entire block of cells it was the Ctrl+Shift+Ente
part I wans’t doing but with your help I got it working.

Thanks again