Intersection of row and column based on input

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CMWalsh - 15 Jun 2006 18:23 GMT

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.

Signature

Thanks,

Chris

Reply to this Message

Barb Reinhardt - 15 Jun 2006 18:31 GMT

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.

> 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:
[quoted text clipped - 14 lines]
>
> Hopefully you can help me out.

Reply to this Message

CMWalsh - 15 Jun 2006 21:56 GMT

I tried these prior to posting in the group.

Signature

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.
[quoted text clipped - 17 lines]
> >
> > Hopefully you can help me out.

Reply to this Message

Domenic - 15 Jun 2006 20:03 GMT

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!

Reply to this Message

CMWalsh - 15 Jun 2006 22:43 GMT

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

Signature

Thanks,

Chris

> Assumptions:
>
[quoted text clipped - 32 lines]
> >
> > Hopefully you can help me out.

Reply to this Message

Domenic - 15 Jun 2006 23:21 GMT

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

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

Reply to this Message