Match Both Vertical and Horizontal Conditons

[GaryS]

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)

 

[T. Valko]

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.

 

[GaryS]

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)

 

[Ashish Mathur]

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

 

[T. Valko]

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.

?????