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

"Hummer2097" wrote
in message ...

I have a spreadsheet for calculating the pivot points on stocks, a
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 stock
I'm looking for, which is a little cumbersome. I've added an extra
empty box at the start so I can type in the ticker for the stock I want
in E2. I'd then like it to search along row number 2 and find the
ticker that matches the one I just type in. I'd then like it to
populate the cells D21:F25 with the equivalent cells for the matching
ticker e.g. L21:L25 for AMZN.

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

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

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


--
Hummer2097
------------------------------------------------------------------------
Hummer2097's Profile:
http://www.excelforum.com/member.php...o&userid=27331
View this thread: http://www.excelforum.com/showthread...hreadid=468283