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.

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.

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.

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.

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.