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 »

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 »

Lookup_value as array

lookup_value as array, why lookup over vlookup, multiple lookup_value in single lookup function, sum(lookup) In one of my earlier posts, I had illustrated passing an array for col_index_num in vlookup. Refer Extract and sum vlookup results. We now look at the possibility of passing an array for lookup_value in vlookup. Refer the example below: If we [...]

Read Full Post »

Converting numbers to words

How do we convert numbers to words. Especially useful if you are writing cheques (checks) through printing through a soft file. The logic given below is one for India – it uses crores and lacs. The logic works for all numbers between 1 and 99,99,99,999 (100 crore -1) Say the numbers that need to be [...]

Read Full Post »

Extract and sum vlookup results

multiple vlookup, sum(vlookup), sum vlookup results in a single formula Consider the problem as below: The table gives the sales figures for each of the sales person across five products Say we need to find the total sales for identified salesperson across 3 products – Prod1, Prod2 and Prod5 One way to do this is [...]

Read Full Post »

Lookup to the left

For looking up through vlookup, the value to be returned needs to be to the right of lookup_value. Here is a solution for returning a value to the left of lookup_value using match and offset functions The problem is given in the image sheet below   The H1Y1 performance of certain sales people have to [...]

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 »

Troubleshooting vlookup

In this post I have listed issues that people often face while using vlookup To begin with, here is the syntax for the vlookup function VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) This image shows how the function is used [range_lookup] is an optional parameter and takes the values True or False When the parameter is entered as True [...]

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 »