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 »

Vlookup – col_index_num

A quick trick for people who often use vlookup. Often when the table array is large, calculating the col_index_num can be quite a hassle. Here is an easy way to do this Lets say our table array is B1:M13 and we want vlookup to return result from the last column (column M), here’s how we [...]

Read Full Post »

Unraveling a table

Here is the problem that I faced         I had to extract the entries in the table row-wise and populate this in a single column. Here is one way of doing this: Enter A8=OFFSET($A$2,INT((ROW()-ROW($B$8))/5),MOD((ROW()-ROW$B$8)),5),1,1) Drag down till the entire table is unravelled. What if we had to unravel the table column wise [...]

Read Full Post »

Generating a series of numbers

In some of my earlier posts, I have manually populated some cells with a series of numbers (numbers 1-4 were manually populated in the post multiple extraction through vlookup). Here is a way we can do away with this by using functions row() and column() Lets say you want to populate 0 to 10 in [...]

Read Full Post »

Multiple extraction through vlookup

The problem here is extraction of multiple results from a two column array through a vlookup   The data available is given in columns B and D. The 4 regions along with their major cities are known. The data needs to be arranged as in the table below Start by sorting on region (column B) [...]

Read Full Post »