How to Unlock MS Excel lookup power to the next level? Part 3
Previously on How to Unlock MS Excel lookup power to the next level, we talked about how to leverage If/ Filter/ Sort to boost the power of vlookup/ xlookup functions. In this article, we will continue to dive in the beauty of MS Excel.
Tab A to C: See Part 1
Tab D: Unique product list
Q3: How to create a pivot view to show quantity by product and UOM using lookup function?
Q3 Solutions
Method 1 (vlookup)
- Return a array where UOM = “Single”/ “Half-Dozen” / “One Dozen”
- Use vlookup to get the quantity by product and UOM.
VLOOKUP($M3,FILTER('Tab C'!$B$4:$D$68,'Tab C'!$C$4:$C$68=N$2),3,FALSE)
Method 2 (xlookup)
- Concate product and UOM to looks up the combination of column B and C of tab C.
- Return the value of column D of tab C.
XLOOKUP($G3&$H$2,'Tab C'!$B$4:$B$68&'Tab C'!$C$4:$C$68,'Tab C'!$D$4:$D$68)Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)
