Excel - HLOOKUP on multiple rows. HOW?

[pho01]

I have a spreadsheet like below. For each month, it has 3 columns, EMEA, NA, and AP. Under those columns, they have several rows as well, like Core Planned, Core Unplanned, Non-core Planned, Non-core Unplanned.

We have a whole year data, and i want to create a section that looks up the data from the whole year and put it in to this section for current month data?

How do I look it up (formula), when it has multiple columns and rows for one lookup value (month) that I want to look up to...

Thanks!

Whole year data ...(due to space, only listing July and August
.........
07/15/2008 08/15/2008
V3 EMEA NA AP EMEA NA AP
Core Planned 89.93 89.93 89.93 78.10 78.10 78.10
Core Unplanned 100.00 100.00 100.00 94.41 94.41 94.41
Non-Core Planned 97.71 97.71 97.71 90.01 90.01 90.01
Non-Core Unplanned 97.71 100.00 100.00 90.01 100.00 100.00

How to look up the current month data? Using something like (HLOOKUP(TODAY(),$B$2:$M$54,2,TRUE)) doesn't work out?

Current Month (This is the data I want to get)
V3 EMEA NA AP
Core Planned
Core Unplanned
Non-Core Planned
Non-Core Unplanned

 

[jdhilljr]

I would use VLOOKUP. The $A3:$AX9 is the range where the data exists. The counta counts to locate the end of entered data in the first row.


the first cell value EMEA row and copy down
=VLOOKUP(A1,$A3:$AX9,COUNTA($B1:$AX3)-1,0)
2nd value NA copy down
=VLOOKUP(A1,$A3:$AX9,COUNTA($B1:$AX3),0)
3rd value AP and copy down
=VLOOKUP(A1,$A3:$AX9,COUNTA($B1:$AX3)+1,0)

Hope this helps.


If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim

 

[GlennUK]

Use INDEX and MATCH. Once you've found the column position where the data is, you can offset it by 1 or 2 to retrieve the other related data.
E.g.

=INDEX($B$3:$N$3,MATCH(required_date,$B$1:$N$1,0))
=INDEX($B$3:$N$3,MATCH(required_date,$B$1:$N$1,0)+1)
=INDEX($B$3:$N$3,MATCH(required_date,$B$1:$N$1,0)+2)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[pho01]

Thank you both for your response. GlennUK response worked great!