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. [...]

Read Full Post »

Cell value from a grid

While we can use vlookup or hlookup to return a cell value in a particular row or column. Is there a way we can lookup from both row and column to return a particular value from a cell in a grid? Consider the case below: (Yes, I have added more colours to the table this [...]

Read Full Post »

The trick of ––

You read it right. Its going to be a quick post but at the end of it, you would be glad that you read it. Trick1: Text to Number  Consider this =ISNUMBER(LEFT(12345,1)) – this returns FALSE as this simplifies to =ISNUMBER(“1″) However, =ISNUMBER(––LEFT(12345,1)) returns TRUE. The two minus signs in conjunction “––” converts text “1″ [...]

Read Full Post »

Extracting the nth occurence from an array

In one of the earlier posts, I extracted the first, last, min and max occurence from an array for a particular lookup. Refer this post. The next step perhaps would be to answer if it is possible to extract the second, third occurences and so on. Consequently to extract all occurences for a particular lookup_value. [...]

Read Full Post »

Sum – Year to Date

The problem here is summing sales figure from the start of the year to a particular month Sales of 2 products (Item1 and Item2) is given for an entire year – Jan to Dec. The objective is to find cummulative sales till a particular month. For instance the input is Item1 and Jun – where [...]

Read Full Post »