Find row value based on four criteria!!

Bonz

Board Regular
Joined
Dec 10, 2007
Messages
149
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
BCDEFGHIJKLM
6Changes--->>>>>Site 3JanFebMarAprMayJunJul
7ElectricityMeter 1
8ElectricityMeter 22987
9TargetElectricityMeter 3
10ElectricityMeter 4
11ElectricityMeter 5
12GasMeter 1
13GasMeter 2
14GasMeter 3
15GasMeter 4
16WaterMeter 1
17WaterMeter 2
18WaterMeter 3
19WaterMeter 4
20WaterMeter 5
21WaterMeter 6
22
23SitesUtilitiesMetersJanFebMarAprMayJun
24Site 1ElectricityMeter 1Tracked000025140
25SourceSite 1GasMeter 1Tracked00006880
26Site 1WaterMeter 1Tracked00008670
27Site 2ElectricityMeter 1Tracked000000
28Site 2GasMeter 1Tracked0007268680
29Site 3ElectricityMeter 1Tracked0019969312840
30Site 3ElectricityMeter 2Tracked00115816829870
31Site 3ElectricityMeter 3Tracked0738009770
32Site 3ElectricityMeter 4Tracked00891116521410
33Site 3ElectricityMeter 5Tracked0625096318770
34Site 3GasMeter 1Tracked2419140033550
35Site 3WaterMeter 1Tracked0097602210
Sheet1
Excel 2007
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Taking up your exhibit and assuming that you want a multi-conditional total (overview) per month...

G7, just enter, copy across, and down:
Code:
=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

Board Regular
Joined
Dec 10, 2007
Messages
149
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

Board Regular
Joined
Dec 10, 2007
Messages
149

ADVERTISEMENT

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
JKLMNOPQ
32Site 1JanFebMarAprMay
33ElectricityMeter 1500#VALUE!#VALUE!#VALUE!#VALUE!
34ElectricityMeter 221#VALUE!#VALUE!#VALUE!#VALUE!
DetailReport
Excel 2007
Cell Formulas
RangeFormula
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
NameRefers 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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

Board Regular
Joined
Dec 10, 2007
Messages
149

ADVERTISEMENT

I'm doing that now.
 

Bonz

Board Regular
Joined
Dec 10, 2007
Messages
149
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

Board Regular
Joined
Dec 10, 2007
Messages
149
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!!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,823
Messages
5,513,593
Members
408,963
Latest member
Joao Corvina

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top