How to Unlock MS Excel lookup power to the next level? Part 2

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 on two more questions and dive in the beauty of MS Excel.

Tab A to C: See Part 1

Q2 Solutions

Method 1 (vlookup)

Since vlookup returns the first match so we can leverage “SORT” function to return highest Qty.

SORT returns a sorted array of the elements in an array. The returned array is the same shape as the provided array argument. 

= SORT(array,[sort_index],[sort_order],[by_col])
array: Required, The range, or array to sort.
sort_index: Optional, A number indicating the row or column to sort by.
sort_order: Optional, A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order.
by_col: Optional, A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column.

• UOM with largest Qty

=VLOOKUP(A3,SORT('Tab A'!$B$4:$E$68,3,-1),2,FALSE)

• Max Qty

=VLOOKUP(A3,SORT('Tab A'!$B$4:$E$68,3,-1),4,FALSE)