| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Help With HLOOK
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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" wrote in message ... 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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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" wrote in message ... 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 |
|
#7
|
|||
|
|||
|
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. -- Hummer2097 ------------------------------------------------------------------------ Hummer2097's Profile: http://www.excelforum.com/member.php...o&userid=27331 View this thread: http://www.excelforum.com/showthread...hreadid=468283 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| hlook function | Excel Worksheet Functions | |||
Linear Mode
