Lookup with vertical and horizontal criteria

Hi guys,

Need your help here, Please see attached for a clearer view.
THANK YOU......

Try this:

Formula:

Please Login or Register  to view this content.

Here is your formula

=SUMPRODUCT(($A$4:$A$13=O3)*($B$4:$B$13=P3)*($C$3:$L$3=Q3)*($C$4:$L$13))

Originally Posted by AlKey

Here is your formula

=SUMPRODUCT(($A$4:$A$13=O3)*($B$4:$B$13=P3)*($C$3:$L$3=Q3)*($C$4:$L$13))

I tried a sumproduct first and got a #VALUE! error. This is the formula I used. Can you see why it wouldn't work?

Formula:

Please Login or Register  to view this content.

Originally Posted by gak67

I tried a sumproduct first and got a #VALUE! error. This is the formula I used. Can you see why it wouldn't work?

Formula:

Please Login or Register  to view this content.

If you tried it and it gave you an error it means that you did something wrong.

here is the result

	O	P	Q	R	S
2	ACCT	CENTER	MONTH
3	510200	15H001-510	JAN	200
4	520300	15H001-511	FEB	410
5	510200	15H001-520	MARCH	720
6	520300	15H001-521	APR	930
7	510100	18H001-510	MAY	140
8	510300	18H001-511	JUNE	350
9	520400	18H001-512	JULY	560
10	510100	18H001-520	AUG	670
11	510300	18H001-521	SEPT	880
12	520400	18H001-522	OCT	1090

Yes I know your formula gives the required result. I was under the impression that the -- before the criteria was the same as multiplying the criteria in a sumproduct function, but one (yours) gives the correct answer and the other (mine) doesn't. I was hoping for more insight than being told I did something wrong.

I have however now done my own research and when using 2 different axis in a sumproduct the -- option does not work.

@gak67

I must have misread your comments and I thought that you were referring to my formula that is giving an error (it actually does happen. Please accept my apology as I had no intention to offend you in any way.

Wasn't offended (maybe a little miffed ), but thanks for the apology.

Hi Guyz,

you all have a brilliant minds, thanks for this,
chil guyz.....l

Originally Posted by clabulis

Here's an OFFSET/MATCH option (also with ctrl+shift+enter):

=OFFSET($A$3,MATCH(O3&P3,A$4:A$13&B$4:B$13,),MATCH(Q3,B$3:L$3,))

You can also do it without Ctrl+Shift+Enter

=OFFSET($A$3,INDEX(MATCH(O3&P3,A$4:A$13&B$4:B$13,),),MATCH(Q3,B$3:L$3,),)