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 2007
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 |