Thread: Intersection of row and column based on input

I have a problem. I am trying to get a function to return a value from a
table on a different worksheet. Here is a small example of my spreadsheet:

Table with Data Keyed in called Input Sheet:
May-06 Jun-06 Jul-06 Aug-06
Rev
S&W
T&E
Promo

My Output sheet is called By Month. The column and row headings are the
same. What I am looking to do is have the output sheet find the data on the
input sheet based on what month I specify. For instance, on the output sheet,
cell A3 is where I put in what month I want the formula to reference. So if I
type in Jan-06, I want the formula to see that and return the value where Rev
interesects with Jan-06 from the input sheet.

Hopefully you can help me out.

--
Thanks,

Chris

You'll probably need to use VLOOKUP and MATCH to get this to work. I don't
have time to describe it more. Maybe someone else will.

"CMWalsh" wrote:

> I have a problem. I am trying to get a function to return a value from a
> table on a different worksheet. Here is a small example of my spreadsheet:
>
> Table with Data Keyed in called Input Sheet:
> May-06 Jun-06 Jul-06 Aug-06
> Rev
> S&W
> T&E
> Promo
>
> My Output sheet is called By Month. The column and row headings are the
> same. What I am looking to do is have the output sheet find the data on the
> input sheet based on what month I specify. For instance, on the output sheet,
> cell A3 is where I put in what month I want the formula to reference. So if I
> type in Jan-06, I want the formula to see that and return the value where Rev
> interesects with Jan-06 from the input sheet.
>
> Hopefully you can help me out.
>
> --
> Thanks,
>
> Chris

Assumptions:

Input!B1:E1 contains the date

Input!A2:A5 contains the category, such as Rev, S&W, etc.

'By Month'!A3 contains the date of interest

'By Month'!B3 contains the category of interest

Formula:

On your sheet named 'By Month'...

=INDEX(Input!B2:E5,MATCH(B3,Input!A2:A5,0),MATCH(A3,Input!B1:E1,0))

Hope this helps!

In article <C96E2DFA-8CE4-4A21-8390-97B288EBF215@microsoft.com>,
CMWalsh <CMWalsh@discussions.microsoft.com> wrote:

> I have a problem. I am trying to get a function to return a value from a
> table on a different worksheet. Here is a small example of my spreadsheet:
>
> Table with Data Keyed in called Input Sheet:
> May-06 Jun-06 Jul-06 Aug-06
> Rev
> S&W
> T&E
> Promo
>
> My Output sheet is called By Month. The column and row headings are the
> same. What I am looking to do is have the output sheet find the data on the
> input sheet based on what month I specify. For instance, on the output sheet,
> cell A3 is where I put in what month I want the formula to reference. So if I
> type in Jan-06, I want the formula to see that and return the value where Rev
> interesects with Jan-06 from the input sheet.
>
> Hopefully you can help me out.

I tried these prior to posting in the group.
--
Thanks,

Chris


"Barb Reinhardt" wrote:

> You'll probably need to use VLOOKUP and MATCH to get this to work. I don't
> have time to describe it more. Maybe someone else will.
>
> "CMWalsh" wrote:
>
> > I have a problem. I am trying to get a function to return a value from a
> > table on a different worksheet. Here is a small example of my spreadsheet:
> >
> > Table with Data Keyed in called Input Sheet:
> > May-06 Jun-06 Jul-06 Aug-06
> > Rev
> > S&W
> > T&E
> > Promo
> >
> > My Output sheet is called By Month. The column and row headings are the
> > same. What I am looking to do is have the output sheet find the data on the
> > input sheet based on what month I specify. For instance, on the output sheet,
> > cell A3 is where I put in what month I want the formula to reference. So if I
> > type in Jan-06, I want the formula to see that and return the value where Rev
> > interesects with Jan-06 from the input sheet.
> >
> > Hopefully you can help me out.
> >
> > --
> > Thanks,
> >
> > Chris

Domenic,
This seems as if this should have worked. However, I am still geting the N/A
error message.
--
Thanks,

Chris


"Domenic" wrote:

> Assumptions:
>
> Input!B1:E1 contains the date
>
> Input!A2:A5 contains the category, such as Rev, S&W, etc.
>
> 'By Month'!A3 contains the date of interest
>
> 'By Month'!B3 contains the category of interest
>
> Formula:
>
> On your sheet named 'By Month'...
>
> =INDEX(Input!B2:E5,MATCH(B3,Input!A2:A5,0),MATCH(A3,Input!B1:E1,0))
>
> Hope this helps!
>
> In article <C96E2DFA-8CE4-4A21-8390-97B288EBF215@microsoft.com>,
> CMWalsh <CMWalsh@discussions.microsoft.com> wrote:
>
> > I have a problem. I am trying to get a function to return a value from a
> > table on a different worksheet. Here is a small example of my spreadsheet:
> >
> > Table with Data Keyed in called Input Sheet:
> > May-06 Jun-06 Jul-06 Aug-06
> > Rev
> > S&W
> > T&E
> > Promo
> >
> > My Output sheet is called By Month. The column and row headings are the
> > same. What I am looking to do is have the output sheet find the data on the
> > input sheet based on what month I specify. For instance, on the output sheet,
> > cell A3 is where I put in what month I want the formula to reference. So if I
> > type in Jan-06, I want the formula to see that and return the value where Rev
> > interesects with Jan-06 from the input sheet.
> >
> > Hopefully you can help me out.
>

If you'd like, I can take a look at your file. Or, if you'd prefer, I
can email you a sample file...

In article <7DB2CD2B-D2E9-4921-8A63-F17AEC6CB1CB@microsoft.com>,
CMWalsh <CMWalsh@discussions.microsoft.com> wrote:

> Domenic,
> This seems as if this should have worked. However, I am still geting the N/A
> error message.
> --
> Thanks,
>
> Chris