How to Unlock MS Excel lookup power to the next level? Part 4
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
Q4: How to get the quantity of cake-like product from the list of tab D?
Q4 Solutions
Basic (vlookup)
- string contains “cake”
IFNA(VLOOKUP("*"&$J$3&"*",'Tab D'!A$2:B$54,1,0),"--Not Found--")- string ends with “cake”
IFNA(VLOOKUP("*"&$J$3,'Tab D'!A$2:B$54,1,0),"--Not Found--")- string starts with “cake”
IFNA(VLOOKUP($J$3&"*",'Tab D'!A$2:B$54,1,0),"--Not Found--")Once get the product, use vlookup to look up quantity.
Advanced (index table + vlookup)
- Using the aboveentioned method is only able to return the FIRST match so if we can’t get the remaining list if there are more than one matched results.
- We are going to create a match list by using INDEX / AGGREGATE/ SEARCH functions.
AGGREGATE:
Returns an aggregate in a list or database.
The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.SEARCH:
Use the SEARCH function in Excel to find the position of a substring in a string.
The SEARCH function is case-insensitive and supports wildcards.- In this match list, we would like to find the product contains “cake” sequentially from the list of tab D.
- Let’s break the process down into the following pieces:
0.Define name tags
pdlist= A2:A54 (product list)
search = D2 (search term, e.g. "cake")
len = D5 (list length)1.Find the poistion of the substring “cake” in “pdlist”.
SEARCH(search,pdlist)
2. Leverage ISNUMBER to determine if the abovementioned returned result.
ISNUMBER(SEARCH(search,pdlist))
3. Identify the sequence of matched items. For example, the first match “Buche de Noel (Christmas Cake)- Winter” is located in the 7th of product list (pdlist). For unmatched items, it will errors.
(ROW(pdlist)-ROW($A$2)+1)/ISNUMBER(SEARCH(search,pdlist))
4. Use AGGREGATE to return the smallest value and ignore the error values to remove the error values.
AGGREGATE(15,6,(ROW(pdlist)-ROW($A$2) + 1)
/ ISNUMBER(SEARCH(search,pdlist)),F2)
5. Use INDEX to match value based on the sequence of matched items in the product list (pdlist).
IFERROR(IF(F2>len,"",INDEX(pdlist,AGGREGATE(15,6,(ROW(pdlist)-ROW($A$2) + 1) / ISNUMBER(SEARCH(search,pdlist)),F2))), "-end of list-")
Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)
