Formula Help - 3 Way lookup that uses Header Row

Johnny Thunder

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. 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:

Excel Sample.xlsx
ABCDEFGHIJKLM
1
2
3AutoMotoEVAerialTotalPercentageAutoMotoEVAerial
4Q1'23Q1'23Q1'23Q1'23Q1'23Q2'23Q2'23Q2'23Q2'23
5TerritoryItemItem No.Q1'23 AutoQ1'23 MotoQ1'23 EVQ1'23 AerialQ1'23 TotalPercentageQ2'23 AutoQ2'23 MotoQ2'23 EVQ2'23 Aerial
6USWrenchT2b9X7fR11698208.180587358.93177558214061149.1115%2543973.6158477.272094042837567.5
7USScrewdriveraP3dK8mL008879340.708879340.79%20748390.99920684.880
8USHammergC4hD1sN000000%0000
9USPliersuH7vT9cQ560154.81033747.250593902.061%0000
10USSocket seteL6zF2xG000000%36096.28000
11USAdjustable wrenchwV5yS4jM860918.9745680648.805533747.2501575315.032%64913.30777331720.262200
12USHex key setdN2kA1tJ000000%0000
13USWire stripperxU6cZ9pV13178.2400013178.240%33689.43000
14USVise grip plierssE4nG5rB89728.077961671621.95233747.2501795097.282%254100.24481525433.80500
15USCordless drillrM8tH7pK000000%0000
16USCombination wrench setjD1vZ5kW000000%0000
17USLocking plierscP7xL2bQ2971276.672585019.0733747.2505590042.996%32490.392382993683.85800
18USTape measuretF2wY6zR000000%0000
19USChiselyV3jG6dC-0.002415799106553.872400106553.870%0000
20USHacksawfT9nW8sH000000%10108.21000
21USHandsawqA5zK1rE24086000240860%39371.42000
22USLevelvS4hD7pN000000%0000
23USCrowbaruG6nC9jX000000%0000
24USSnipsmW8kZ2xJ000000%0000
25USUtility knifebR1tF3yL000000%0000
26USPutty knifeiN7dP5sQ000000%0000
27USStaple gunpX9vT4cM12250.7700012250.770%16929.02000
28USNeedle-nose plierszE2rH6fK000000%0000
29USAwloB3sG8nA000000%0000
30USClampsaY4jL6xT000000%0000
31USMiter sawkU2vC7dQ3498594.270003498594.274%51996.94000
32USJigsawlT9wS5nM8828671.8193983.7233747.2509056402.7710%321755.3906229884.749400
33USCircular sawhP1zF7kR000000%0000
Data


The template that would need to show the formula results in column F:

Excel Sample.xlsx
ABCDEF
1LocationItemItem No. CategoryPeriodAmount
2USWrenchT2b9X7fRAutoQ2'232837567.5
3USScrewdriveraP3dK8mLAutoQ2'230
4USHammergC4hD1sNAutoQ2'230
5USPliersuH7vT9cQAutoQ2'230
6USSocket seteL6zF2xGAutoQ2'230
7USAdjustable wrenchwV5yS4jMAutoQ2'230
8USHex key setdN2kA1tJAutoQ2'230
9USWire stripperxU6cZ9pVAutoQ2'230
10USVise grip plierssE4nG5rBAutoQ2'230
11USCordless drillrM8tH7pKAutoQ2'230
12USCombination wrench setjD1vZ5kWAutoQ2'230
13USLocking plierscP7xL2bQAutoQ2'230
14USTape measuretF2wY6zRAutoQ2'230
15USChiselyV3jG6dCAutoQ2'230
16USHacksawfT9nW8sHAutoQ2'230
17USHandsawqA5zK1rEAutoQ2'230
18USLevelvS4hD7pNAutoQ2'230
19USCrowbaruG6nC9jXAutoQ2'230
20USSnipsmW8kZ2xJAutoQ2'230
21USUtility knifebR1tF3yLAutoQ2'230
22USPutty knifeiN7dP5sQAutoQ2'230
23USStaple gunpX9vT4cMAutoQ2'230
24USNeedle-nose plierszE2rH6fKAutoQ2'230
25USAwloB3sG8nAAutoQ2'230
26USClampsaY4jL6xTAutoQ2'230
27USMiter sawkU2vC7dQAutoQ2'230
28USJigsawlT9wS5nMAutoQ2'230
29USCircular sawhP1zF7kRAutoQ2'230
Template
Cell Formulas
RangeFormula
F2:F29F2=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)))
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
row 5 seems to be a combination of row 3 and 4

would this work for you

=INDEX(data!$D$6:$N$33,MATCH(C2,data!$C$6:$C$33,0),MATCH(E2&" "&D2,data!$D$5:$M$5,0))

not touched either sheet or changed anything

Book2
ABCDEF
1LocationItemItem No. CategoryPeriodAmount
2USWrenchT2b9X7fRAutoQ2'232543973.61
3USScrewdriveraP3dK8mLAutoQ2'2320748390.99
4USHammergC4hD1sNAutoQ2'230
5USPliersuH7vT9cQAutoQ2'230
6USSocket seteL6zF2xGAutoQ2'2336096.28
7USAdjustable wrenchwV5yS4jMAutoQ2'2364913.30777
8USHex key setdN2kA1tJAutoQ2'230
9USWire stripperxU6cZ9pVAutoQ2'2333689.43
10USVise grip plierssE4nG5rBAutoQ2'23254100.2448
11USCordless drillrM8tH7pKAutoQ2'230
12USCombination wrench setjD1vZ5kWAutoQ2'230
13USLocking plierscP7xL2bQAutoQ2'2332490.39238
14USTape measuretF2wY6zRAutoQ2'230
15USChiselyV3jG6dCAutoQ2'230
16USHacksawfT9nW8sHAutoQ2'2310108.21
17USHandsawqA5zK1rEAutoQ2'2339371.42
18USLevelvS4hD7pNAutoQ2'230
19USCrowbaruG6nC9jXAutoQ2'230
20USSnipsmW8kZ2xJAutoQ2'230
21USUtility knifebR1tF3yLAutoQ2'230
22USPutty knifeiN7dP5sQAutoQ2'230
23USStaple gunpX9vT4cMAutoQ2'2316929.02
24USNeedle-nose plierszE2rH6fKAutoQ2'230
25USAwloB3sG8nAAutoQ2'230
26USClampsaY4jL6xTAutoQ2'230
27USMiter sawkU2vC7dQAutoQ2'2351996.94
28USJigsawlT9wS5nMAutoQ2'23321755.3906
29USCircular sawhP1zF7kRAutoQ2'230
Sheet1
Cell Formulas
RangeFormula
F2:F29F2=INDEX(data!$D$6:$N$33,MATCH(C2,data!$C$6:$C$33,0),MATCH(E2&" "&D2,data!$D$5:$M$5,0))
 
Upvote 0
etaf said:
row 5 seems to be a combination of row 3 and 4

would this work for you

=INDEX(data!$D$6:$N$33,MATCH(C2,data!$C$6:$C$33,0),MATCH(E2&" "&D2,data!$D$5:$M$5,0))

not touched either sheet or changed anything

Book2
ABCDEF
1LocationItemItem No. CategoryPeriodAmount
2USWrenchT2b9X7fRAutoQ2'232543973.61
3USScrewdriveraP3dK8mLAutoQ2'2320748390.99
4USHammergC4hD1sNAutoQ2'230
5USPliersuH7vT9cQAutoQ2'230
6USSocket seteL6zF2xGAutoQ2'2336096.28
7USAdjustable wrenchwV5yS4jMAutoQ2'2364913.30777
8USHex key setdN2kA1tJAutoQ2'230
9USWire stripperxU6cZ9pVAutoQ2'2333689.43
10USVise grip plierssE4nG5rBAutoQ2'23254100.2448
11USCordless drillrM8tH7pKAutoQ2'230
12USCombination wrench setjD1vZ5kWAutoQ2'230
13USLocking plierscP7xL2bQAutoQ2'2332490.39238
14USTape measuretF2wY6zRAutoQ2'230
15USChiselyV3jG6dCAutoQ2'230
16USHacksawfT9nW8sHAutoQ2'2310108.21
17USHandsawqA5zK1rEAutoQ2'2339371.42
18USLevelvS4hD7pNAutoQ2'230
19USCrowbaruG6nC9jXAutoQ2'230
20USSnipsmW8kZ2xJAutoQ2'230
21USUtility knifebR1tF3yLAutoQ2'230
22USPutty knifeiN7dP5sQAutoQ2'230
23USStaple gunpX9vT4cMAutoQ2'2316929.02
24USNeedle-nose plierszE2rH6fKAutoQ2'230
25USAwloB3sG8nAAutoQ2'230
26USClampsaY4jL6xTAutoQ2'230
27USMiter sawkU2vC7dQAutoQ2'2351996.94
28USJigsawlT9wS5nMAutoQ2'23321755.3906
29USCircular sawhP1zF7kRAutoQ2'230
Sheet1
Cell Formulas
RangeFormula
F2:F29F2=INDEX(data!$D$6:$N$33,MATCH(C2,data!$C$6:$C$33,0),MATCH(E2&" "&D2,data!$D$5:$M$5,0))
Click to expand...
Worked perfectly! Thank you for your help on that! So I guess maybe excel doesn't like looking at formulas in headers more than once.
 
Upvote 0
Johnny Thunder said:
So I guess maybe excel doesn't like looking at formulas in headers more than once.
Click to expand...
probably does , I did not try to work it out, since the row 5 - seemed to be the same as the 2 headers - so a simpler formula

i remember now I have done 2 columns before ... i may look into over the next couple of days

anyway - glad its worked that way
 
Upvote 1
Hi Etaf, wanted to ask if you know why adding a 3rd variable causes the formula to fail?

I added column B into the mix and now I am getting a #REF! error and not sure why?

=INDEX(Data!$D$6:$N$33,MATCH(C2,Data!$C$6:$C$33,0),MATCH(B2,Data!$B$6:$B$33,0),MATCH(E2&" "&D2,Data!$D$5:$M$5,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
173
purekarting
P
R
  • Solved
Connect periods with spilled result, where the result is same as the calculated result in previous row
2
Replies
15
Views
175
roelandwatteeuw
R
JenniferMurphy
  • Solved
Is there a better way to format table headers?
Replies
9
Views
163
shift-del
shift-del
sanantonio
  • Solved
Returning the second, third, etc. value on lookup
Replies
7
Views
527
Fluff
Fluff
J
  • Question
Help writing an XLOOKUP formula using tables and Headers
Replies
1
Views
506
Fluff
Fluff

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top