Match Both Vertical and Horizontal Conditons
 

I'm trying to figure out a formula the would match a month on the verital
column and then three sets of critera in the horizontal column such as year
and two other words (BGT & TRN).

So for instance is I want to match the date and year from A5 & B5 plus the
words BGT & TRN in and return the value D5 from the above data and would
return 100.


A B C D
2009 2008
BGT BGT
TRN TRN
1 JAN 100 301
2 FEB 101 401
3 MAR 201 501


5 JAN 2009 * (Match the date from A5 & B5 with Value in C5)

Match Both Vertical and Horizontal Conditons
 

Ok, we'll need some clarifications...

Is this what your table looks like:

...........A..........B..........C
1.................2009.....2008
2.................BGT......BGT
3.................TRN......TRN
4......JAN....100........301
5......FEB....101........401
6......MAR...201.......501

5 JAN 2009

Is that in 2 cells?

5 Jan
2009

Is 5 Jan a true Excel date?

Are the months in your table in a calender sequence Jan, Feb, Mar, Apr, May,
Jun, Jul, ... Dec

If BGT TRN is entered in every column then all you need to do is match the
year number.


--
Biff
Microsoft Excel MVP


"GaryS" wrote in message
...
I'm trying to figure out a formula the would match a month on the verital
column and then three sets of critera in the horizontal column such as
year
and two other words (BGT & TRN).

So for instance is I want to match the date and year from A5 & B5 plus the
words BGT & TRN in and return the value D5 from the above data and would
return 100.


A B C D
2009 2008
BGT BGT
TRN TRN
1 JAN 100 301
2 FEB 101 401
3 MAR 201 501


5 JAN 2009 * (Match the date from A5 & B5 with Value in C5)

Match Both Vertical and Horizontal Conditons
 

Here's a better picture of the cells.... thanks for the reply. To answer your
question yes I'm using excel dates... the 5 was supposed to represent row 5
with a Month and Year in two different cells. There are lots of other cells
feeding the monthly totals below. And yes the months do go in Calendar
sequence, however it's dynamic report that the first month changes based on
the current month looking twelve months forward. Also I'd have to match Year,
BGT, and TRN because I have three sets of columns: For 2007,2008,2009 then
in each year I have Budget, Forecast and Actual. Finally in each of them I
have three segments called Transient, Group, and Contract. I hope this helps
and thank you again for your help, much appreciated.

..............A.................B..............C.. ................D..............E
1............................2009.........2008.... .......2009..........2008
2............................BGT...........BGT.... .........FCT..........FCT
3...........................TRN............GRP.... ........TRN.........GRP
4...........JAN...........101.............301..... ........750.........111
5...........FEB...........101.............401..... ........850.........222
6...........MAR..........201.............501...... .......950.........333
7................................................. ........................................
8.............................................TRN. ..............FCT
9..........JAN.............2008...........*....... ............*
..............(*Return Values)


"T. Valko" wrote:

Ok, we'll need some clarifications...

Is this what your table looks like:

...........A..........B..........C
1.................2009.....2008
2.................BGT......BGT
3.................TRN......TRN
4......JAN....100........301
5......FEB....101........401
6......MAR...201.......501

5 JAN 2009

Is that in 2 cells?

5 Jan
2009

Is 5 Jan a true Excel date?

Are the months in your table in a calender sequence Jan, Feb, Mar, Apr, May,
Jun, Jul, ... Dec

If BGT TRN is entered in every column then all you need to do is match the
year number.


--
Biff
Microsoft Excel MVP


"GaryS" wrote in message
...
I'm trying to figure out a formula the would match a month on the verital
column and then three sets of critera in the horizontal column such as
year
and two other words (BGT & TRN).

So for instance is I want to match the date and year from A5 & B5 plus the
words BGT & TRN in and return the value D5 from the above data and would
return 100.


A B C D
2009 2008
BGT BGT
TRN TRN
1 JAN 100 301
2 FEB 101 401
3 MAR 201 501


5 JAN 2009 * (Match the date from A5 & B5 with Value in C5)

Match Both Vertical and Horizontal Conditons
 

Hi,

Try this

SUMPRODUCT(($B$5:$B$7=$B14)*($C$2:$F$2=$C14)*(($C$ 3:$F$3=D$13)+($C$4:$F$4=D$13)),$C$5:$F$7)

I have assumed that the data is in range B2:F7.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"GaryS" wrote in message
...
Here's a better picture of the cells.... thanks for the reply. To answer
your
question yes I'm using excel dates... the 5 was supposed to represent row
5
with a Month and Year in two different cells. There are lots of other
cells
feeding the monthly totals below. And yes the months do go in Calendar
sequence, however it's dynamic report that the first month changes based
on
the current month looking twelve months forward. Also I'd have to match
Year,
BGT, and TRN because I have three sets of columns: For 2007,2008,2009
then
in each year I have Budget, Forecast and Actual. Finally in each of them I
have three segments called Transient, Group, and Contract. I hope this
helps
and thank you again for your help, much appreciated.

.............A.................B..............C... ...............D..............E
1............................2009.........2008.... .......2009..........2008
2............................BGT...........BGT.... .........FCT..........FCT
3...........................TRN............GRP.... ........TRN.........GRP
4...........JAN...........101.............301..... ........750.........111
5...........FEB...........101.............401..... ........850.........222
6...........MAR..........201.............501...... .......950.........333
7................................................. ........................................
8.............................................TRN. ..............FCT
9..........JAN.............2008...........*....... ............*
.............(*Return Values)


"T. Valko" wrote:

Ok, we'll need some clarifications...

Is this what your table looks like:

...........A..........B..........C
1.................2009.....2008
2.................BGT......BGT
3.................TRN......TRN
4......JAN....100........301
5......FEB....101........401
6......MAR...201.......501

5 JAN 2009

Is that in 2 cells?

5 Jan
2009

Is 5 Jan a true Excel date?

Are the months in your table in a calender sequence Jan, Feb, Mar, Apr,
May,
Jun, Jul, ... Dec

If BGT TRN is entered in every column then all you need to do is match
the
year number.


--
Biff
Microsoft Excel MVP


"GaryS" wrote in message
...
I'm trying to figure out a formula the would match a month on the
verital
column and then three sets of critera in the horizontal column such as
year
and two other words (BGT & TRN).

So for instance is I want to match the date and year from A5 & B5 plus
the
words BGT & TRN in and return the value D5 from the above data and
would
return 100.


A B C D
2009 2008
BGT BGT
TRN TRN
1 JAN 100 301
2 FEB 101 401
3 MAR 201 501


5 JAN 2009 * (Match the date from A5 & B5 with Value in C5)

Match Both Vertical and Horizontal Conditons
 

OK, I'm still confused!

Based on the updated table there is no entry for JAN 2008 TRN.

For JAN 2008 FCT I guess the result should be 111.

?????

--
Biff
Microsoft Excel MVP


"GaryS" wrote in message
...
Here's a better picture of the cells.... thanks for the reply. To answer
your
question yes I'm using excel dates... the 5 was supposed to represent row
5
with a Month and Year in two different cells. There are lots of other
cells
feeding the monthly totals below. And yes the months do go in Calendar
sequence, however it's dynamic report that the first month changes based
on
the current month looking twelve months forward. Also I'd have to match
Year,
BGT, and TRN because I have three sets of columns: For 2007,2008,2009
then
in each year I have Budget, Forecast and Actual. Finally in each of them I
have three segments called Transient, Group, and Contract. I hope this
helps
and thank you again for your help, much appreciated.

.............A.................B..............C... ...............D..............E
1............................2009.........2008.... .......2009..........2008
2............................BGT...........BGT.... .........FCT..........FCT
3...........................TRN............GRP.... ........TRN.........GRP
4...........JAN...........101.............301..... ........750.........111
5...........FEB...........101.............401..... ........850.........222
6...........MAR..........201.............501...... .......950.........333
7................................................. ........................................
8.............................................TRN. ..............FCT
9..........JAN.............2008...........*....... ............*
.............(*Return Values)


"T. Valko" wrote:

Ok, we'll need some clarifications...

Is this what your table looks like:

...........A..........B..........C
1.................2009.....2008
2.................BGT......BGT
3.................TRN......TRN
4......JAN....100........301
5......FEB....101........401
6......MAR...201.......501

5 JAN 2009

Is that in 2 cells?

5 Jan
2009

Is 5 Jan a true Excel date?

Are the months in your table in a calender sequence Jan, Feb, Mar, Apr,
May,
Jun, Jul, ... Dec

If BGT TRN is entered in every column then all you need to do is match
the
year number.


--
Biff
Microsoft Excel MVP


"GaryS" wrote in message
...
I'm trying to figure out a formula the would match a month on the
verital
column and then three sets of critera in the horizontal column such as
year
and two other words (BGT & TRN).

So for instance is I want to match the date and year from A5 & B5 plus
the
words BGT & TRN in and return the value D5 from the above data and
would
return 100.


A B C D
2009 2008
BGT BGT
TRN TRN
1 JAN 100 301
2 FEB 101 401
3 MAR 201 501


5 JAN 2009 * (Match the date from A5 & B5 with Value in C5)