Forum Discussion
VLOOKUP returning sum of values with common value in one row
=LET(rng,B7:J20,
detailed_info,DROP(rng,,2),
so_pr,CHOOSECOLS(rng,1),
unique_so_pr,UNIQUE(so_pr),
HSTACK(
UNIQUE(TAKE(rng,,2)),
UNIQUE(DROP(REDUCE("",SEQUENCE(ROWS(unique_so_pr)),
LAMBDA(a,b,VSTACK(a,
REDUCE("",SEQUENCE(COLUMNS(DROP(rng,,2))),
LAMBDA(u,v,
LET(z,FILTER(CHOOSECOLS(FILTER(detailed_info,so_pr=INDEX(unique_so_pr,b)),v),LEN(CHOOSECOLS(FILTER(detailed_info,so_pr=INDEX(unique_so_pr,b)),v))>0),
HSTACK(u,IF(ISNUMBER(z),SUM(z),z))))))))
,1,1))))
With Office 365 or Excel for the web this formula could be an alternative.
Thanks a bunch! But when I try to replicate on real case, it doesn't work.
Raw Data:
| SO PR | Sold-To | Billing Material | Billing Quantity | Billing Amount | Supplier | Invoice Material | Invoice Quantity | Invoice Amount |
| 110350588 | Superman | Krypton | 20,600.000 | 23,297.18 | 0.000 | 0.00 | ||
| 110350588 | 0.000 | 0.00 | Captain America | Krypton | 20,600.000 | 4,845.38 | ||
| 110350591 | Batman | Krypton | 23,900.000 | 31,308.89 | 0.000 | 0.00 | ||
| 110350591 | 0.000 | 0.00 | Captain America | Krypton | 23,900.000 | 7,157.84 | ||
| 110350596 | Ironman | Xenon | 22,600.000 | 25,074.38 | 0.000 | 0.00 | ||
| 110350596 | 0.000 | 0.00 | Captain America | Xenon | 22,600.000 | 6,768.51 |
Results:
| SO PR | Sold-To | Billing Material | Billing Quantity | Billing Amount | Supplier | Invoice Material | Invoice Quantity | Invoice Amount |
| 110350588 | Superman | Krypton | 20600 | 23297.18 | Captain America | Krypton | 20600 | 4845.38 |
| 110350588 | #N/A | 20600 | 23297.18 | #N/A | #N/A | 20600 | 4845.38 | |
| 110350591 | Batman | Krypton | 23900 | 31308.89 | Captain America | Krypton | 23900 | 7157.84 |
| 110350591 | #N/A | 23900 | 31308.89 | #N/A | #N/A | 23900 | 7157.84 | |
| 110350596 | Ironman | Xenon | 22600 | 25074.38 | Captain America | Xenon | 22600 | 6768.51 |
| 110350596 | #N/A | 22600 | 25074.38 | #N/A | #N/A | 22600 | 6768.51 |
Here's the file. Appreciate your kindness to investigate. Thanks!
You are welcome! Do you work with google sheets? The formula works in Office 365 or Excel for the web.