Lookup for “contains”

Here is a method for looking up “contains” from the lookup value array.
Consider the problem below. I have sales figures for Asia Pacific countries. The lookup value extracted from the system is in the format “Country City”

The expected results are given in the table D1:E11.

Here is the solution using search and sumproduct. You may use array function instead of sumproduct. Suit yourself.

E2=SUMPRODUCT(––ISNUMBER(SEARCH(D2,CountryCity)),Sales)

where, CountryCity is A2:A14 and Sales is B2:B14

You do not need to change the formula while computing for a country figure. The same formula works perfectly.

Now, how does this formula work,

• Search function looks for the lookup city or country in all cells of CountryCity. (You could use Find function but it is case-sensitive). For cells which contains the lookup city or country it returns a number
• Isnumber converts this to True for cells containing the lookup value. Rest are False
• The operator –– converts True to 1 and False to 0 (Refer earlier post)
• Sumproduct multiplies the array of 1’s and 0’s with the Sales array and returns the summation of Sales figure where the “contains” for lookup value is True

Have a great weekend 🙂

Read Full Post »