Help with HLOOKUP/INDEX/MATCH

[WembleyBear]

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

 

[akphidelt]

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)

 

[T. Valko]

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?

 

[wembleybear]

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

 

[wembleybear]

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

Martyn

 

[T. Valko]

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

 

[Roger Govier]

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)