Which Group does a name belong to?

Consider this problem

The table above gives the groups in which each of the members belong. the problem here is to find out in which group one of the member belongs to e.g. Keith (A12). The formula used in B12 is

=INDEX($A$1:$C$1,1,SUMPRODUCT(- –ISNUMBER(SEARCH(A12,$A$2:$C$8)),{1,2,3}*{1;1;1;1;1;1;1}))

The way this works is

• Search function searches for Keith (A12) in the array A2:C8. It returns #VALUE! for cells where Keith is not present and 1 for the cell in which Keith is present
• Isnumber returns True for the cell in which Keith is present and False for the rest
• Operator – – changes True and False to numbers 1 and 0 respectively
• Thus we have an array of 0s and one 1 with 7 rows and 3 columns
• We now generate an array with {1,2,3}*{1;1;1;1;1;1;1}. This array is of the form

• Sumproduct multiplies the earlier generated array of 0 and 1 with our array such that it returns the column number of cell in which Keith is present – 2 in this case
• Index simply returns the equivalent group from the header array A1:C1 – Lions in this case.

Can you figure out a way of not putting the array manually by using the function row and column functions.

Read Full Post »

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 »