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 »

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 »

First, Last, Min, Max for a particular value in an array

Consider a set-up where 4 users randomly appear for a test. Their scores are recorded in the order in which they appear for the test. Our objective is to find their scores in the first and last attempts and also their minimum and maximum scores. Here are the users and their scores: The objective is [...]

Read Full Post »

Text to Numbers

Just like that, each alphabet corresponds to a number, summing up alphabets I cant really see how this post could be useful to you. One of my friends who believed in numerology brought this problem to me of converting names to numbers. Here is the solution that we worked out. You may treat this as [...]

Read Full Post »

Insert spaces – revisited

I had earlier posted a solution on inserting spaces between continuous cells in a list. Though the solution works fine for a few items (46 items in the earlier example), it returns a blank for items after that. The solution had comprised of using the formula C1+1/3; where C1=1 and dragging it so that two [...]

Read Full Post »

Create spaces within a list

This is a very interesting problem that we encountered. To simplify the problem, the objective of the exercise was to insert spaces within a continuous list. For example   Column A has the original list and column D has the desired result. Two blank cells needed to be inserted between the entries. To solve this problem, [...]

Read Full Post »