Maybe you can you use INDEX and MATCH as described he
http://support.microsoft.com/kb/214142

The general idea is that you put the month names in Row 1 of Worksheet 2 and
your metrics in Column A of Worksheet 2. Then in Worksheet 1 you have a
formula that will look for the matching month and metric, then use INDEX to
grab the value from the corresponding cell with where the matching row and
column intersect.

There is a specific example on the web page at the IP address above, to help
you build the formula you need.

Tip: For an exact match, be sure to use "0" (zero) as the third argument of
your MATCH function.

"ralph" wrote:

I have a workbook with 2 worksheets, 1 sheet is a simple summary showing the
current months data and the ytd data (the month would be input by the user)
as below. The second sheet will have the month by month information and the
ytd.

How do I get it to lookup according to month inputted and then pick up the
correct corresponding data

WORKSHEET 1
Enter Month "Apr"
Month YTD
Metric 1
Metric 2
metric 3

WORKSHEET 2
Jan Feb Mar APr....... YTD
Metric 1
Metric 2
Metric 3

thanks
Ralph