Using XLookup on two lists with VStack and Filter

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
152
Office Version
  1. 365
Platform
  1. Windows
Hello. I have been looking into using Xlookup for data on two different lists. I managed to find a solution on Google but can't get it to work following several failed attempts. I am trying to get the sales for a product in cell G1. A bonus would be, as a separate exercise, if i can use SUMIFS to get a sum of sales for product A (appears on both lists). Many thanks!
 

Attachments

  • XLOOKUP.PNG
    XLOOKUP.PNG
    21.5 KB · Views: 11

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
try using FILTER instead of XLOOKUP, for all your scenarios.

Book1
ABCDEFGH
1ProductG
2ProductUnitsProductUnits6581Using cell G1
3ProductA1050ProductA8506581Prod G
4ProductB2000ProductG65811900Prod A
5ProductC500ProductH951
6ProductD2500ProductI15000
Sheet5
Cell Formulas
RangeFormula
G2G2=SUM(LET(arr,VSTACK($A$3:$B$6,$D$3:$E$6),trgt,"Product"&$G$1,prd,TAKE(arr,,1),amts,TAKE(arr,,-1),FILTER(amts,prd=trgt,"")))
G3G3=SUM(LET(arr,VSTACK($A$3:$B$6,$D$3:$E$6),trgt,"Product"&"G",prd,TAKE(arr,,1),amts,TAKE(arr,,-1),FILTER(amts,prd=trgt,"")))
G4G4=SUM(LET(arr,VSTACK($A$3:$B$6,$D$3:$E$6),trgt,"Product"&"A",prd,TAKE(arr,,1),amts,TAKE(arr,,-1),FILTER(amts,prd=trgt,"")))
 
Upvote 0
another option...
MrExcel_20240224.xlsx
ABCDEFGH
1List 1List 2
2ProductUnitsProductUnits951H
3A1050A8501900A
4B2000G6581
5C500H951
6D2500I1500
Sheet7
Cell Formulas
RangeFormula
G2:G3G2=LET(comb,VSTACK($A$3:$B$6,$D$3:$E$6),SUM(FILTER(TAKE(comb,,-1),TAKE(comb,,1)=$H2)))

Edit:...essentially the same as @awoohaw's in a slightly different order
 
Upvote 0
another option...
MrExcel_20240224.xlsx
ABCDEFGH
1List 1List 2
2ProductUnitsProductUnits951H
3A1050A8501900A
4B2000G6581
5C500H951
6D2500I1500
Sheet7
Cell Formulas
RangeFormula
G2:G3G2=LET(comb,VSTACK($A$3:$B$6,$D$3:$E$6),SUM(FILTER(TAKE(comb,,-1),TAKE(comb,,1)=$H2)))

Edit:...essentially the same as @awoohaw's in a slightly different order
and more concise, i realized I could have made it smaller after I posted.
 
Upvote 0
KRice & awoohaw, Thank you for your prompt replies. The solutions are a bit different from what i was expecting, but they both work for me, so win is a win. Once again, many thanks!
 
Upvote 0
KRice & awoohaw, Thank you for your prompt replies. The solutions are a bit different from what i was expecting, but they both work for me, so win is a win. Once again, many thanks!
well, xlookup will not find a second value so that was not going to be in a solution. You're welcome. Best wishes.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

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