Hi

I have a worksheet where the user enters the month they are interested in
the data for from a drop-down box A2. I then had a HLOOKUP to find the value
from the column for that month. This worked fine. The problem is, each month
is now going to have 2 columns under it (one for this year & one for last
year) and from what I've read HLOOKUP may no longer be the right solution and
perhaps INDEX &/or MATCH would be better but I'm struggling with the how the
formula would read.

The spreadsheet now looks like this below, with month names in row 1 being
centred across each pair of year columns in row 2. My formula goes in row 3
under the Export column. When the user selects, say January in cell A2, then
in row 3 under Export 2008 it should read 55. If they changed the month to
February, the formula should return 72.


Month January February Export
January 2008 2007 2008 2007 2008 2007
55 111 72 19


Any help with this would be very much appreciated!

Thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4

Try using offset and match

For instance under Export and 2008 put the formula

=Offset(A1,2,Match(A2,B1:X1,0))

Then for the 2007 just add a simple + 1 to the match like

=Offset(A1,2,Match(A2,B1:X1,0)+1)

"WembleyBear" wrote:

Hi

I have a worksheet where the user enters the month they are interested in
the data for from a drop-down box A2. I then had a HLOOKUP to find the value
from the column for that month. This worked fine. The problem is, each month
is now going to have 2 columns under it (one for this year & one for last
year) and from what I've read HLOOKUP may no longer be the right solution and
perhaps INDEX &/or MATCH would be better but I'm struggling with the how the
formula would read.

The spreadsheet now looks like this below, with month names in row 1 being
centred across each pair of year columns in row 2. My formula goes in row 3
under the Export column. When the user selects, say January in cell A2, then
in row 3 under Export 2008 it should read 55. If they changed the month to
February, the formula should return 72.


Month January February Export
January 2008 2007 2008 2007 2008 2007
55 111 72 19


Any help with this would be very much appreciated!

Thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4

month names in row 1 being centred across
each pair of year columns in row 2.

The month name will be referenced from the leftmost cell and HLOOKUP will
find the leftmost instance so HLOOKUP should still work.

Did you try it and it didn't work?

--
Biff
Microsoft Excel MVP


"WembleyBear" wrote in message
...
Hi

I have a worksheet where the user enters the month they are interested in
the data for from a drop-down box A2. I then had a HLOOKUP to find the
value
from the column for that month. This worked fine. The problem is, each
month
is now going to have 2 columns under it (one for this year & one for last
year) and from what I've read HLOOKUP may no longer be the right solution
and
perhaps INDEX &/or MATCH would be better but I'm struggling with the how
the
formula would read.

The spreadsheet now looks like this below, with month names in row 1 being
centred across each pair of year columns in row 2. My formula goes in row
3
under the Export column. When the user selects, say January in cell A2,
then
in row 3 under Export 2008 it should read 55. If they changed the month to
February, the formula should return 72.


Month January February Export
January 2008 2007 2008 2007 2008 2007
55 111 72 19


Any help with this would be very much appreciated!

Thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4

Hi

Yes, it does work when using HLOOKUP but because my month heading is merged
over the two year columns the result is as you say, just the leftmost
column. This is fine for 2008 column but I also need to bring the value from
the 2007 column as well into the corresponding Export column, and that's
where I'm stuck with HLOOKUP and thought INDEX and MATCH might be the answer
though I've not used them before.

Martyn




"T. Valko" wrote in message
...
month names in row 1 being centred across
each pair of year columns in row 2.

The month name will be referenced from the leftmost cell and HLOOKUP will
find the leftmost instance so HLOOKUP should still work.

Did you try it and it didn't work?

--
Biff
Microsoft Excel MVP


"WembleyBear" wrote in message
...
Hi

I have a worksheet where the user enters the month they are interested in
the data for from a drop-down box A2. I then had a HLOOKUP to find the
value
from the column for that month. This worked fine. The problem is, each
month
is now going to have 2 columns under it (one for this year & one for last
year) and from what I've read HLOOKUP may no longer be the right solution
and
perhaps INDEX &/or MATCH would be better but I'm struggling with the how
the
formula would read.

The spreadsheet now looks like this below, with month names in row 1
being
centred across each pair of year columns in row 2. My formula goes in row
3
under the Export column. When the user selects, say January in cell A2,
then
in row 3 under Export 2008 it should read 55. If they changed the month
to
February, the formula should return 72.


Month January February Export
January 2008 2007 2008 2007 2008 2007
55 111 72 19


Any help with this would be very much appreciated!

Thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4

Excellent - that did the trick. Thanks very much for your help!!

Martyn


"akphidelt" wrote in message
...
Try using offset and match

For instance under Export and 2008 put the formula

=Offset(A1,2,Match(A2,B1:X1,0))

Then for the 2007 just add a simple + 1 to the match like

=Offset(A1,2,Match(A2,B1:X1,0)+1)

"WembleyBear" wrote:

Hi

I have a worksheet where the user enters the month they are interested in
the data for from a drop-down box A2. I then had a HLOOKUP to find the
value
from the column for that month. This worked fine. The problem is, each
month
is now going to have 2 columns under it (one for this year & one for last
year) and from what I've read HLOOKUP may no longer be the right solution
and
perhaps INDEX &/or MATCH would be better but I'm struggling with the how
the
formula would read.

The spreadsheet now looks like this below, with month names in row 1
being
centred across each pair of year columns in row 2. My formula goes in row
3
under the Export column. When the user selects, say January in cell A2,
then
in row 3 under Export 2008 it should read 55. If they changed the month
to
February, the formula should return 72.


Month January February Export
January 2008 2007 2008 2007 2008 2007
55 111 72 19


Any help with this would be very much appreciated!

Thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4

Post your current HLOOKUP formula so I can see where everything is. Use
range references and not range names.

--
Biff
Microsoft Excel MVP


"wembleybear" wrote in message
...
Hi

Yes, it does work when using HLOOKUP but because my month heading is
merged over the two year columns the result is as you say, just the
leftmost column. This is fine for 2008 column but I also need to bring the
value from the 2007 column as well into the corresponding Export column,
and that's where I'm stuck with HLOOKUP and thought INDEX and MATCH might
be the answer though I've not used them before.

Martyn




"T. Valko" wrote in message
...
month names in row 1 being centred across
each pair of year columns in row 2.

The month name will be referenced from the leftmost cell and HLOOKUP will
find the leftmost instance so HLOOKUP should still work.

Did you try it and it didn't work?

--
Biff
Microsoft Excel MVP


"WembleyBear" wrote in message
...
Hi

I have a worksheet where the user enters the month they are interested
in
the data for from a drop-down box A2. I then had a HLOOKUP to find the
value
from the column for that month. This worked fine. The problem is, each
month
is now going to have 2 columns under it (one for this year & one for
last
year) and from what I've read HLOOKUP may no longer be the right
solution and
perhaps INDEX &/or MATCH would be better but I'm struggling with the how
the
formula would read.

The spreadsheet now looks like this below, with month names in row 1
being
centred across each pair of year columns in row 2. My formula goes in
row 3
under the Export column. When the user selects, say January in cell A2,
then
in row 3 under Export 2008 it should read 55. If they changed the month
to
February, the formula should return 72.


Month January February Export
January 2008 2007 2008 2007 2008 2007
55 111 72 19


Any help with this would be very much appreciated!

Thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4

Hi Martyn

I would make life easier, by using Jan 07, Jan 08, Feb 07 etc. in row 2
Make your dropdown list in A2, have Jan 07 etc as well, then the Hlookup
will work fine, basing it on Row2 with an offset of 2
=HLOOKUP(A2,B2:E3,2,0)

--
Regards
Roger Govier

"wembleybear" wrote in message
...
Hi

Yes, it does work when using HLOOKUP but because my month heading is
merged over the two year columns the result is as you say, just the
leftmost column. This is fine for 2008 column but I also need to bring the
value from the 2007 column as well into the corresponding Export column,
and that's where I'm stuck with HLOOKUP and thought INDEX and MATCH might
be the answer though I've not used them before.

Martyn




"T. Valko" wrote in message
...
month names in row 1 being centred across
each pair of year columns in row 2.

The month name will be referenced from the leftmost cell and HLOOKUP will
find the leftmost instance so HLOOKUP should still work.

Did you try it and it didn't work?

--
Biff
Microsoft Excel MVP


"WembleyBear" wrote in message
...
Hi

I have a worksheet where the user enters the month they are interested
in
the data for from a drop-down box A2. I then had a HLOOKUP to find the
value
from the column for that month. This worked fine. The problem is, each
month
is now going to have 2 columns under it (one for this year & one for
last
year) and from what I've read HLOOKUP may no longer be the right
solution and
perhaps INDEX &/or MATCH would be better but I'm struggling with the how
the
formula would read.

The spreadsheet now looks like this below, with month names in row 1
being
centred across each pair of year columns in row 2. My formula goes in
row 3
under the Export column. When the user selects, say January in cell A2,
then
in row 3 under Export 2008 it should read 55. If they changed the month
to
February, the formula should return 72.


Month January February Export
January 2008 2007 2008 2007 2008 2007
55 111 72 19


Any help with this would be very much appreciated!

Thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4