Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hello all,
I have a formula question that I am super confused about because I honestly thought the formula solution I came up with would work but it doesn't and not sure why.
So essentially, I need a formula that compare 3 pieces of criteria (Item No., Category and Period) and populate the data into a sheet called template. I guess the only tricky part is that 2 of the arrays for the criteria are headers and not vertical columns.
I tried SUMPRODUCT, XLOOKUP and INDEX/MATCH and all three seem to result in the values coming back with a 0 or an error? Not sure why but the data is posted below.
Any help is appreciated!
My Data:
The template that would need to show the formula results in column F:
I have a formula question that I am super confused about because I honestly thought the formula solution I came up with would work but it doesn't and not sure why.
So essentially, I need a formula that compare 3 pieces of criteria (Item No., Category and Period) and populate the data into a sheet called template. I guess the only tricky part is that 2 of the arrays for the criteria are headers and not vertical columns.
I tried SUMPRODUCT, XLOOKUP and INDEX/MATCH and all three seem to result in the values coming back with a 0 or an error? Not sure why but the data is posted below.
Any help is appreciated!
My Data:
Excel Sample.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | |||||||||||||||
2 | |||||||||||||||
3 | Auto | Moto | EV | Aerial | Total | Percentage | Auto | Moto | EV | Aerial | |||||
4 | Q1'23 | Q1'23 | Q1'23 | Q1'23 | Q1'23 | Q2'23 | Q2'23 | Q2'23 | Q2'23 | ||||||
5 | Territory | Item | Item No. | Q1'23 Auto | Q1'23 Moto | Q1'23 EV | Q1'23 Aerial | Q1'23 Total | Percentage | Q2'23 Auto | Q2'23 Moto | Q2'23 EV | Q2'23 Aerial | ||
6 | US | Wrench | T2b9X7fR | 11698208.18 | 0 | 587358.93 | 1775582 | 14061149.11 | 15% | 2543973.61 | 58477.27 | 209404 | 2837567.5 | ||
7 | US | Screwdriver | aP3dK8mL | 0 | 0 | 8879340.7 | 0 | 8879340.7 | 9% | 20748390.99 | 920684.88 | 0 | |||
8 | US | Hammer | gC4hD1sN | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
9 | US | Pliers | uH7vT9cQ | 560154.81 | 0 | 33747.25 | 0 | 593902.06 | 1% | 0 | 0 | 0 | 0 | ||
10 | US | Socket set | eL6zF2xG | 0 | 0 | 0 | 0 | 0 | 0% | 36096.28 | 0 | 0 | 0 | ||
11 | US | Adjustable wrench | wV5yS4jM | 860918.9745 | 680648.8055 | 33747.25 | 0 | 1575315.03 | 2% | 64913.30777 | 331720.2622 | 0 | 0 | ||
12 | US | Hex key set | dN2kA1tJ | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
13 | US | Wire stripper | xU6cZ9pV | 13178.24 | 0 | 0 | 0 | 13178.24 | 0% | 33689.43 | 0 | 0 | 0 | ||
14 | US | Vise grip pliers | sE4nG5rB | 89728.07796 | 1671621.952 | 33747.25 | 0 | 1795097.28 | 2% | 254100.2448 | 1525433.805 | 0 | 0 | ||
15 | US | Cordless drill | rM8tH7pK | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
16 | US | Combination wrench set | jD1vZ5kW | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
17 | US | Locking pliers | cP7xL2bQ | 2971276.67 | 2585019.07 | 33747.25 | 0 | 5590042.99 | 6% | 32490.39238 | 2993683.858 | 0 | 0 | ||
18 | US | Tape measure | tF2wY6zR | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
19 | US | Chisel | yV3jG6dC | -0.002415799 | 106553.8724 | 0 | 0 | 106553.87 | 0% | 0 | 0 | 0 | 0 | ||
20 | US | Hacksaw | fT9nW8sH | 0 | 0 | 0 | 0 | 0 | 0% | 10108.21 | 0 | 0 | 0 | ||
21 | US | Handsaw | qA5zK1rE | 24086 | 0 | 0 | 0 | 24086 | 0% | 39371.42 | 0 | 0 | 0 | ||
22 | US | Level | vS4hD7pN | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
23 | US | Crowbar | uG6nC9jX | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
24 | US | Snips | mW8kZ2xJ | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
25 | US | Utility knife | bR1tF3yL | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
26 | US | Putty knife | iN7dP5sQ | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
27 | US | Staple gun | pX9vT4cM | 12250.77 | 0 | 0 | 0 | 12250.77 | 0% | 16929.02 | 0 | 0 | 0 | ||
28 | US | Needle-nose pliers | zE2rH6fK | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
29 | US | Awl | oB3sG8nA | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
30 | US | Clamps | aY4jL6xT | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
31 | US | Miter saw | kU2vC7dQ | 3498594.27 | 0 | 0 | 0 | 3498594.27 | 4% | 51996.94 | 0 | 0 | 0 | ||
32 | US | Jigsaw | lT9wS5nM | 8828671.8 | 193983.72 | 33747.25 | 0 | 9056402.77 | 10% | 321755.3906 | 229884.7494 | 0 | 0 | ||
33 | US | Circular saw | hP1zF7kR | 0 | 0 | 0 | 0 | 0 | 0% | 0 | 0 | 0 | 0 | ||
Data |
The template that would need to show the formula results in column F:
Excel Sample.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Location | Item | Item No. | Category | Period | Amount | ||
2 | US | Wrench | T2b9X7fR | Auto | Q2'23 | 2837567.5 | ||
3 | US | Screwdriver | aP3dK8mL | Auto | Q2'23 | 0 | ||
4 | US | Hammer | gC4hD1sN | Auto | Q2'23 | 0 | ||
5 | US | Pliers | uH7vT9cQ | Auto | Q2'23 | 0 | ||
6 | US | Socket set | eL6zF2xG | Auto | Q2'23 | 0 | ||
7 | US | Adjustable wrench | wV5yS4jM | Auto | Q2'23 | 0 | ||
8 | US | Hex key set | dN2kA1tJ | Auto | Q2'23 | 0 | ||
9 | US | Wire stripper | xU6cZ9pV | Auto | Q2'23 | 0 | ||
10 | US | Vise grip pliers | sE4nG5rB | Auto | Q2'23 | 0 | ||
11 | US | Cordless drill | rM8tH7pK | Auto | Q2'23 | 0 | ||
12 | US | Combination wrench set | jD1vZ5kW | Auto | Q2'23 | 0 | ||
13 | US | Locking pliers | cP7xL2bQ | Auto | Q2'23 | 0 | ||
14 | US | Tape measure | tF2wY6zR | Auto | Q2'23 | 0 | ||
15 | US | Chisel | yV3jG6dC | Auto | Q2'23 | 0 | ||
16 | US | Hacksaw | fT9nW8sH | Auto | Q2'23 | 0 | ||
17 | US | Handsaw | qA5zK1rE | Auto | Q2'23 | 0 | ||
18 | US | Level | vS4hD7pN | Auto | Q2'23 | 0 | ||
19 | US | Crowbar | uG6nC9jX | Auto | Q2'23 | 0 | ||
20 | US | Snips | mW8kZ2xJ | Auto | Q2'23 | 0 | ||
21 | US | Utility knife | bR1tF3yL | Auto | Q2'23 | 0 | ||
22 | US | Putty knife | iN7dP5sQ | Auto | Q2'23 | 0 | ||
23 | US | Staple gun | pX9vT4cM | Auto | Q2'23 | 0 | ||
24 | US | Needle-nose pliers | zE2rH6fK | Auto | Q2'23 | 0 | ||
25 | US | Awl | oB3sG8nA | Auto | Q2'23 | 0 | ||
26 | US | Clamps | aY4jL6xT | Auto | Q2'23 | 0 | ||
27 | US | Miter saw | kU2vC7dQ | Auto | Q2'23 | 0 | ||
28 | US | Jigsaw | lT9wS5nM | Auto | Q2'23 | 0 | ||
29 | US | Circular saw | hP1zF7kR | Auto | Q2'23 | 0 | ||
Template |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F29 | F2 | =INDEX(Data!$D$6:$N$33,MATCH(C2,Data!$C$6:$C$33,0),MATCH(E2,Data!$D$4:$M$4,MATCH(D2,Data!$D$3:$M$3,0))) |