Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and hlookup
I have a table which is static that contains figures from all months of the
year for individual items. J F M A M J..... Item 1 10 20 15 10 5 7 Item 2 7 58 45 25 4 24 If on another table (which is dyanmic - the months will change each quarter) Month 1 Month 2 Month 3 Item 1 ________ ________ ________ Item 2 ________ ________ ________ I want the underlined cells to reference the same item from the other table for whichever month is displayed in the 'month1" cell. I assume that i need to use both the vlookup and hlookup functions because i want it to look up the "item" through vlookup and the month with hlookup, but i am not familiar with how the hlookup works or how to combine the two into the same formula. Can anyone help me with this? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and hlookup
In your first table, set dates to be 01/01/2006 (Jan),01/02/2006 (Feb) etc
and format as Custom=mmm In your second table, format dates as above for required quarter. Assuming data in table 1 starts in cell B2 , then in cell B2 of second table put the following and copy across/down as required =INDEX(Sheet1!$B$2:$G$100,MATCH($A2,Sheet1!$A$2:$A $100,0),MATCH(B$1,Sheet1!$B$1:$N$1,0)) HTH "hotelmasters" wrote: I have a table which is static that contains figures from all months of the year for individual items. J F M A M J..... Item 1 10 20 15 10 5 7 Item 2 7 58 45 25 4 24 If on another table (which is dyanmic - the months will change each quarter) Month 1 Month 2 Month 3 Item 1 ________ ________ ________ Item 2 ________ ________ ________ I want the underlined cells to reference the same item from the other table for whichever month is displayed in the 'month1" cell. I assume that i need to use both the vlookup and hlookup functions because i want it to look up the "item" through vlookup and the month with hlookup, but i am not familiar with how the hlookup works or how to combine the two into the same formula. Can anyone help me with this? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and hlookup
I appreciate the help, but to be honest i have no idea what you mean.
"Toppers" wrote: In your first table, set dates to be 01/01/2006 (Jan),01/02/2006 (Feb) etc and format as Custom=mmm In your second table, format dates as above for required quarter. Assuming data in table 1 starts in cell B2 , then in cell B2 of second table put the following and copy across/down as required =INDEX(Sheet1!$B$2:$G$100,MATCH($A2,Sheet1!$A$2:$A $100,0),MATCH(B$1,Sheet1!$B$1:$N$1,0)) HTH "hotelmasters" wrote: I have a table which is static that contains figures from all months of the year for individual items. J F M A M J..... Item 1 10 20 15 10 5 7 Item 2 7 58 45 25 4 24 If on another table (which is dyanmic - the months will change each quarter) Month 1 Month 2 Month 3 Item 1 ________ ________ ________ Item 2 ________ ________ ________ I want the underlined cells to reference the same item from the other table for whichever month is displayed in the 'month1" cell. I assume that i need to use both the vlookup and hlookup functions because i want it to look up the "item" through vlookup and the month with hlookup, but i am not familiar with how the hlookup works or how to combine the two into the same formula. Can anyone help me with this? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and hlookup
E-mail me at toppers<atjohntopley.fsnet.co.uk and I'll post you an example
spreadsheet. "hotelmasters" wrote: I appreciate the help, but to be honest i have no idea what you mean. "Toppers" wrote: In your first table, set dates to be 01/01/2006 (Jan),01/02/2006 (Feb) etc and format as Custom=mmm In your second table, format dates as above for required quarter. Assuming data in table 1 starts in cell B2 , then in cell B2 of second table put the following and copy across/down as required =INDEX(Sheet1!$B$2:$G$100,MATCH($A2,Sheet1!$A$2:$A $100,0),MATCH(B$1,Sheet1!$B$1:$N$1,0)) HTH "hotelmasters" wrote: I have a table which is static that contains figures from all months of the year for individual items. J F M A M J..... Item 1 10 20 15 10 5 7 Item 2 7 58 45 25 4 24 If on another table (which is dyanmic - the months will change each quarter) Month 1 Month 2 Month 3 Item 1 ________ ________ ________ Item 2 ________ ________ ________ I want the underlined cells to reference the same item from the other table for whichever month is displayed in the 'month1" cell. I assume that i need to use both the vlookup and hlookup functions because i want it to look up the "item" through vlookup and the month with hlookup, but i am not familiar with how the hlookup works or how to combine the two into the same formula. Can anyone help me with this? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and hlookup
Your first table would like the one below. The cell showing "Jan-o6" has
01/01/2006 entered ut is formatted to show as "Jan-06". To do this click "Format", select "Cells" then "Custom" and enter "mmm-yy2 (without quotes) in the "Type" entry box. Highlight the cell, place cursor on solid square at bottom right of cell which change to a cross; hold down left mouse button and copy for required number of months which will update automatically (to Dec-06) TABLE1 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Item 1 10 20 15 10 5 Item 2 7 58 45 25 4 Item 3 14 27 48 30 25 Item 4 39 25 43 19 34 Repeat above for your second table (shown below to get the quartley months as shown below. Enter the formula into the first "results" cell i.e where 15 is shown below, and then copy across and down as described above. TABLE2 Mar-06 Apr-06 May-06 Item 1 15 10 5 item 2 45 25 4 Item 3 48 30 25 Item 4 43 19 34 HTH "Toppers" wrote: E-mail me at toppers<atjohntopley.fsnet.co.uk and I'll post you an example spreadsheet. "hotelmasters" wrote: I appreciate the help, but to be honest i have no idea what you mean. "Toppers" wrote: In your first table, set dates to be 01/01/2006 (Jan),01/02/2006 (Feb) etc and format as Custom=mmm In your second table, format dates as above for required quarter. Assuming data in table 1 starts in cell B2 , then in cell B2 of second table put the following and copy across/down as required =INDEX(Sheet1!$B$2:$G$100,MATCH($A2,Sheet1!$A$2:$A $100,0),MATCH(B$1,Sheet1!$B$1:$N$1,0)) HTH "hotelmasters" wrote: I have a table which is static that contains figures from all months of the year for individual items. J F M A M J..... Item 1 10 20 15 10 5 7 Item 2 7 58 45 25 4 24 If on another table (which is dyanmic - the months will change each quarter) Month 1 Month 2 Month 3 Item 1 ________ ________ ________ Item 2 ________ ________ ________ I want the underlined cells to reference the same item from the other table for whichever month is displayed in the 'month1" cell. I assume that i need to use both the vlookup and hlookup functions because i want it to look up the "item" through vlookup and the month with hlookup, but i am not familiar with how the hlookup works or how to combine the two into the same formula. Can anyone help me with this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what are the uses for Vlookup and Hlookup? | Excel Worksheet Functions | |||
can hlookup and vlookup be used in combination ? | Excel Discussion (Misc queries) | |||
VLookup and HLookup at the same time | Excel Worksheet Functions | |||
Vlookup and Hlookup | Excel Worksheet Functions | |||
Can vlookup or hlookup look to other worksheets within a workbook. | Excel Worksheet Functions |