Find row value based on four criteria!!

[Bonz]

Hi All,

I’m trying to find the value in a row based on three other row values and one column value.

For example in cell K8 of the Target I need the value in cell L30 from the source. This would be based on the other color matched values.

I've added the coloring to help illustrate what I'm looking for.

So the following values in target: (Site Utility, meter and month) are used to locate and get the correct value from the Source.

The site value in D6 does change depending on the site I want to drill into; if that matters.

I have the following named ranges in the Source: (Sites, Utilities, Meters & Months) which I have tripled checked and are correct.

After searching the site I managed the following two formulas but can’t get them to work. Any suggestions would be appreciated.



THANKS!


INDEX(MeterDatabase, SUMPRODUCT((Sites=D6),--(Utilities=E8),--(Meters=F8)),MATCH(K6, Months,0))

VLOOKUP(SUMPRODUCT(--(Sites=D6),--(Utilities=E8),--(Meters=F8)),MeterDatabase, MATCH(K6, Months,0))

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
6	Changes--->>>>>		Site 3			Jan	Feb	Mar	Apr	May	Jun	Jul
7				Electricity	Meter 1
8				Electricity	Meter 2					2987
9	Target			Electricity	Meter 3
10				Electricity	Meter 4
11				Electricity	Meter 5
12				Gas	Meter 1
13				Gas	Meter 2
14				Gas	Meter 3
15				Gas	Meter 4
16				Water	Meter 1
17				Water	Meter 2
18				Water	Meter 3
19				Water	Meter 4
20				Water	Meter 5
21				Water	Meter 6
22
23			Sites	Utilities	Meters		Jan	Feb	Mar	Apr	May	Jun
24			Site 1	Electricity	Meter 1	Tracked	0	0	0	0	2514	0
25	Source		Site 1	Gas	Meter 1	Tracked	0	0	0	0	688	0
26			Site 1	Water	Meter 1	Tracked	0	0	0	0	867	0
27			Site 2	Electricity	Meter 1	Tracked	0	0	0	0	0	0
28			Site 2	Gas	Meter 1	Tracked	0	0	0	726	868	0
29			Site 3	Electricity	Meter 1	Tracked	0	0	199	693	1284	0
30			Site 3	Electricity	Meter 2	Tracked	0	0	1158	168	2987	0
31			Site 3	Electricity	Meter 3	Tracked	0	738	0	0	977	0
32			Site 3	Electricity	Meter 4	Tracked	0	0	891	1165	2141	0
33			Site 3	Electricity	Meter 5	Tracked	0	625	0	963	1877	0
34			Site 3	Gas	Meter 1	Tracked	241	914	0	0	3355	0
35			Site 3	Water	Meter 1	Tracked	0	0	976	0	221	0
Sheet1

Excel 2007

 

[Aladin Akyurek]

Taking up your exhibit and assuming that you want a multi-conditional total (overview) per month...

G7, just enter, copy across, and down:

=SUMPRODUCT(
   --($D$24:$D$35=$D$6),
   --($E$24:$E$35=$E7),
   --($F$24:$F$35=$F7),
   INDEX($H$24:$M$35,0,MATCH(G$6,$H$23:$M$23,0)))

 

[Bonz]

Aladin,

Thank you for taking the time. The formula provided worked fine down the first column "Jan" but everything else returned #Value!.

I used Evaluate Formula and everything worked up untill the the last step?

 

[Aladin Akyurek]

Aladin,

Thank you for taking the time. The formula provided worked fine down the first column "Jan" but everything else returned #Value!.

I used Evaluate Formula and everything worked up untill the the last step?

Did you try it as is or adapted it to your workbook? If the latter, care to post the adapted formula?

 

[Bonz]

Modified, I've posted the info. I'm not seeing the problem. Or understand why it works for one column but not the others?

Thanks for helping!

Excel Workbook
	J	K	L	M	N	O	P	Q
32	Site 1			Jan	Feb	Mar	Apr	May
33		Electricity	Meter 1	500	#VALUE!	#VALUE!	#VALUE!	#VALUE!
34		Electricity	Meter 2	21	#VALUE!	#VALUE!	#VALUE!	#VALUE!
DetailReport

Excel 2007

Cell Formulas
Range	Formula
O33	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K33),--(Meter_List=$L33),INDEX(ModMeterDatabase,0,MATCH(O$32,Months,0)))
O34	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K34),--(Meter_List=$L34),INDEX(ModMeterDatabase,0,MATCH(O$32,Months,0)))
P33	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K33),--(Meter_List=$L33),INDEX(ModMeterDatabase,0,MATCH(P$32,Months,0)))
P34	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K34),--(Meter_List=$L34),INDEX(ModMeterDatabase,0,MATCH(P$32,Months,0)))
Q33	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K33),--(Meter_List=$L33),INDEX(ModMeterDatabase,0,MATCH(Q$32,Months,0)))
Q34	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K34),--(Meter_List=$L34),INDEX(ModMeterDatabase,0,MATCH(Q$32,Months,0)))
M33	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K33),--(Meter_List=$L33),INDEX(ModMeterDatabase,0,MATCH(M$32,Months,0)))
M34	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K34),--(Meter_List=$L34),INDEX(ModMeterDatabase,0,MATCH(M$32,Months,0)))
N34	=SUMPRODUCT(--(Site_List=$J$32),--(Utility_List=$K34),--(Meter_List=$L34),INDEX(ModMeterDatabase,0,MATCH(N$32,Months,0)))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook
Name	Refers To
Meter_List	=DetailReport!$L$52:$L$137
ModMeterDatabase	=DetailReport!$N$52:$Y$137
Months	=DetailReport!$N$51:$Y$51
Site_List	=DetailReport!$J$52:G$137
Utility	=OFFSET(LookupLists!$R$2,0,0,COUNTA(LookupLists!$R:$R)-1,1)
Utility_List	=DetailReport!$K$52:$K$137
Workbook Defined Names

 

[Aladin Akyurek]

Bonz,

Would you substitute the real ranges for a moment for just testing for there may be
an issue with the range definitions, provided that there are no #N/A's in the relevant ranges?

By the way, try not to post so many formulas with tha Jeanie thing. And a SumProduct formula
does not ordinarily require applying control+shift+enter.

 

[Bonz]

I'm doing that now.

 

[Bonz]

The following from M33 worked across row 33:

SUMPRODUCT( --($J$52:$J$137=$J$32), --(Utility_List=$K33), --(Meter_List=$L33), INDEX(ModMeterDatabase,0,MATCH($M$32,Months,0)))

As you can the range name auto loaded when I clicked and dragged except for Site_List but in the Name Manager it looks right.

But after changing the relativity of the cell references to go down the column it does not work???

 

[Aladin Akyurek]

The following from M33 worked across row 33:

SUMPRODUCT( --($J$52:$J$137=$J$32), --(Utility_List=$K33), --(Meter_List=$L33), INDEX(ModMeterDatabase,0,MATCH($M$32,Months,0)))

As you can the range name auto loaded when I clicked and dragged except for Site_List but in the Name Manager it looks right.

But after changing the relativity of the cell references to go down the column it does not work???

It should work. The following must hold:

=ROWS($J$52:$J$137)=ROWS(Utility_List)
=ROWS(Utility_List)=ROWS(Meter_List)
=ROWS(Meter_List)=ROWS(ModMeterDatabase)

ModMeterDatabase must be a matrix.

$M$32 --> M$32

It would be so much better if you worked first with exhibit you posted and the formulas provided for that. Once the set up is clear, you can switch to the real data.

 

[Bonz]

Aladin,

The following pulled from the middle of the table works:

=SUMPRODUCT( --($J$52:$J$137=$J$32), --(Utility_List=$K43), --(Meter_List=$L43), INDEX(ModMeterDatabase,0,MATCH(Q$32,Months,0)))

So everything is good, no errors and correct values. The problem was with the named ranges as you suspected. I checked them several times in the Name Manager and they are correct there?

Your formula worked perfectly--not surprising.

Thank you as always for the help.

Very much appreciated!!