Help With HLOOK

[Hummer2097]

I have a spreadsheet for calculating the pivot points on stocks, a screenshot of which can be seen here

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.

[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.

--
Don Guillett
SalesAid Software
donaldb@281.com
"Hummer2097" <Hummer2097.1vgoij_1126890341.7288@excelforum-nospam.com> wrote
in message news:Hummer2097.1vgoij_1126890341.7288@excelforum-nospam.com...
>
> 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
>

[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

"Hummer2097" <Hummer2097.1vgoij_1126890341.7288@excelforum-nospam.com> wrote
in message news:Hummer2097.1vgoij_1126890341.7288@excelforum-nospam.com...
>
> 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
>

[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

"Biff" <biffinpitt@comcast.net> wrote in message
news:enAV3KvuFHA.3100@TK2MSFTNGP12.phx.gbl...
> 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" <Hummer2097.1vgoij_1126890341.7288@excelforum-nospam.com>
> wrote in message
> news:Hummer2097.1vgoij_1126890341.7288@excelforum-nospam.com...
>>
>> 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
>>
>
>

[Hummer2097]

Thanks a lot for the reply. I just used OFFSET and MATCH like you said and put a formulae in each of the 15 boxs like this with a diffent 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 an hour on.

Just saw your reply as well Biff. Thanks for that. I couldn't seem to get it working with the 5,3 and had to have 15 different 1,1 formuals 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" <Hummer2097.1vgzme_1126904736.1774@excelforum-nospam.com> wrote
in message news:Hummer2097.1vgzme_1126904736.1774@excelforum-nospam.com...
>
> Thanks a lot for the reply. I just used OFFSET and MATCH like you said
> and put a formulae in each of the 15 boxs like this with a diffent
> 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 an
> hour on.
>
> Just saw your reply as well Biff. Thanks for that. I couldn't seem to
> get it working with the 5,3 and had to have 15 different 1,1 formuals
> 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.
>
>
> --
> Hummer2097
> ------------------------------------------------------------------------
> Hummer2097's Profile:
> http://www.excelforum.com/member.php...o&userid=27331
> View this thread: http://www.excelforum.com/showthread...hreadid=468283
>

[Hummer2097]

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

Thanks again.