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 time 🙂 )

The construct is such that every week 5 batches of measurement is carried out. The dates are given in cells A2:A14 and batch numbers are given in cells B1:F1.

Result table for measurement for specific dates and batches is given in the table below:

You could use one of the following formulas to arrive at the result:

Method1: vlookup with match

C17=VLOOKUP(A17,A2:F14,MATCH(B17,A1:F1,0),FALSE)

Of course, you could find the same result with hlookup with match. Only remember to lookup in a row instead of a column

Method2: Index with match

C18=INDEX(B2:F14,MATCH(A18,A2:A14,0),MATCH(B18,B1:F1,0))

Method3: Indirect with address and match

C19=INDIRECT(ADDRESS(MATCH(A19,A2:A14,0)+ROW(A2)-1,MATCH(B19,B1:F1,0)+COLUMN(B1)-1))

And now the bonus formula – using array

Method4: Sum as an array function

C20 {=SUM(($A$2:$A$14=A20)*($B$1:$F$1=B20)*B2:F14)}

Array formula is entered with ctrl+shift+enter

Method5: Use sumproduct as an alternate for array function

C21=SUMPRODUCT(($A$2:$A$14=A21)*($B$1:$F$1=B21)*B2:F14)

Use one of the above formulas or make one for yourself and share with others. Have fun 🙂

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 to find the first, last, min and max score for each user. The solution looks like this:

For first attempt score, the formula is a simple vlookup

F2=VLOOKUP(E2,$B$2:$C$15,2,FALSE)

Person is B2:B15 and Score is C2:C15

For max score, the formula is simply:

H2 {=MAX(IF(Person=E2,Score,0))} entered as an array formula with ctrl+shift+enter

Similarly, for min score, the formula is

I2 {=MIN(IF(Person=E2,Score,100))} entered as an array formula with ctrl+shift+enter

I have entered 100 as it is greater than the largest number in Score. You may replace this with max(Score)+1.

Now for last attempt score, you could use one of the 3 sets of formulas

1. Using Index function – array formula

G2 {=INDEX(Score,MAX((Person=E2)*ROW(Person))-ROW(Person)+1)} entered as an array formula with ctrl+shift+enter

In this formula, max function finds out the last row number for a particular user. For instance for user Andy, MAX((Person=E2)*ROW(Person)) returns 14. To return the score for Andy in the last attempt, I have used index function. The minus part of ROW(Person)+1 simply takes care of cells above array Score.

2. Indirect-address function – array formula

G2 {=INDIRECT(ADDRESS(MAX((Person=E2)*ROW(Person)),3))} entered as an array formula with ctrl+shift+enter

If you feel uncomfortable with entering a number in the formula, you could replace 3 with column(Score)

3. Index – Sumproduct function

If you are tired of using array formulas, sumproduct offers some respite

G2=INDEX(Score,SUMPRODUCT(MAX((Person=E2)*ROW(Person)))-ROW(Person)+1)

The formula is similar to the Index with array formula. Here sumproduct is used differently then the normal usage. Notice that there is only one array in sumproduct. The function treats the parameters entered as an array, so we need not enter it as an array formula.

Similarly, indirect address formula can also be entered with sumproduct without entering it as an array formula.

Give it a try. Have fun 🙂

Answer:

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 were required to sum the sale of Prod3 for Angie, Mike and Ross (cells highlighted in yellow), can we do it through a single vlookup function. Lets say an array is defined as {Ross, Mike, Angie} as salesperson.

This vlookup function

{=SUM(VLOOKUP(salesperson,A3:D7,4,FALSE))} entered as an array with ctrl+shift+enter gives the result as 3 – which corresponds to sale of Prod3 by Ross (first element in the array salesperson). So clearly the array function is not working!

Lets now try the lookup function. Revise the above formula as

{=SUM(LOOKUP(salesperson,A3:D7))}

OR

{=SUM(LOOKUP(salesperson,A3:A7,D3:D7))}

which depends on your comfort with the two syntax for lookup

• LOOKUP(lookup_value,array)
• LOOKUP(lookup_value,lookup_vector,result_vector)

entered as an array formula with ctrl+shift+enter

The formula works and it correctly returns 114

The point to notice here is that lookup_array needs to be in ascending order.

Have fun. This is only the second time that I have used lookup function. It also explains why in the previous post – Text to numbers – method 2, I have used lookup and not vlookup.

Have fun 🙂

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 converted to words are given in column A

Step 1: Start with defining the following arrays

Step2: Extract units, tens, hundreds, thousands, lacs and crores from the numbers in column A

The result is given Array1 below:

The formulas used are:

For crores: D2=ROUNDDOWN($A2,-7)

For lacs: E2=ROUNDDOWN($A2,-5)-$D2

For thousands: F2=ROUNDDOWN($A2,-3)-SUM($D2:E2)

For hundreds: G2=ROUNDDOWN($A2,-2)-SUM($D2:F2)

For tens & units: H2=$A2-SUM($D2:G2)

Drag till end of columns

Modify the Array1 so that resuts appear as this in Array2:

The formulas used are:

For crores: I2=D2/10^7

For lacs: J2=E2/10^5

For thousands: K2=F2/10^3

For hundreds: L2=G2/10^2

For tens+units: M2=H2

Step 3: Convert numbers to words for each column

The formulas used are:

Crores: N2=IF(LEN(I2)=1,VLOOKUP(I2,Unit_array,2,FALSE),IF(VALUE(LEFT(I2,1))=1,VLOOKUP(I2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(I2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(I2,1)),Unit_array,2,FALSE))))

Lacs: O2=IF(LEN(J2)=1,VLOOKUP(J2,Unit_array,2,FALSE),IF(VALUE(LEFT(J2,1))=1,VLOOKUP(J2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(J2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(J2,1)),Unit_array,2,FALSE))))

Thousands: P2=IF(LEN(K2)=1,VLOOKUP(K2,Unit_array,2,FALSE),IF(VALUE(LEFT(K2,1))=1,VLOOKUP(K2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(K2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(K2,1)),Unit_array,2,FALSE))))

Hundreds: Q2=VLOOKUP(L2,Unit_array,2,FALSE)

Tens&Units: R2=IF(LEN(M2)=1,VLOOKUP(M2,Unit_array,2,FALSE),IF(VALUE(LEFT(M2,1))=1,VLOOKUP(M2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(M2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(M2,1)),Unit_array,2,FALSE))))

Formula for crores, lacs, thousands, tens&units are similar in logic and construct

The logic used is

• If number is of 1 digit (0 to 9)- then lookup value from Unit_array
• If number starts with 1 (10 to 19) – then lookup value from teens
• For all else (20 to 99) – lookup left digit from tens and right digit from Unit_array and concatenate

Formula for hundreds is simple – lookup value from Unit_array as hundreds can only be from 0 to 9

Step 4: Now the fun part – putting it all together

The formula is simply

B2=PROPER(CONCATENATE(IF(I2>0,N2&” crores “,””),IF(J2>0,O2&” lacs “,””),IF(K2>0,P2&” thousand “,””),IF(L2>0,Q2&” hundred “,””),IF(R2>0,R2,””)))

Have added proper to capitalize the first letters of all words in the text string.

Let me know if it helps you 🙂

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 to carry out vlookup thrice and sum them up. Here is the same solution but with a single vlookup entered as an array formula.

 

 

The formula in cell B10 is {=SUM(VLOOKUP(A10,A3:F7,{2,3,6},FALSE))} entered as an array formula with ctrl+shift+enter

The formula carries out vlookup for each of the col_index_num in the array viz. 2,3 and 6 and then sums the result.

Let me know if it ever speeds up your task 🙂

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 be returned. This is achieved through this formula in cell B10

Method 1: Match with Offset

B10 = OFFSET($C$2,MATCH(A10,$C$3:$C$7,0),-2)

This is then dragged to cover all sales people for which figures are required.

Note: match function returns the placement of lookup_value in the column, this is then used to offset. Note that offset reference (C2) is one cell above the lookup column (C3:C7) to ensure that if match returns 1, offset gives a correct value

Method 2: Sum as an array function

B10 {=SUM(($C$3:$C$7=A10)*$A$3:$A$7)}

entered with ctrl+shift+enter

Method 3: Sumproduct

B10=SUMPRODUCT(–($C$3:$C$7=A10),$A$3:$A$7)

May you find this useful!

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 can do this

Say lookup_value is Data5, we would need to key in this formula

=VLOOKUP(B6,B2:M13,(COLUMN(M1)-COLUMN(B1))+1,FALSE)

col_index_num here is (COLUMN(M1)-COLUMN(B1))+1

The formula correctly gives the result return5

Hope you find this useful 🙂

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 (or omitted)

• The array needs to be sorted in ascending order on the first column
• If exact match is not found, the next largest value that is less than look_up value is returned

When the parameter is entered as False, vlookup finds an exact match

1. On carrying out vlookup with range_lookup as false, the value returned is #N/A, though I am sure that the lookup_value is contained in column1 of table_array, what could be the problem?

One of the likely cause of this error is that lookup_value contained in table_array is preceded or succeeded by empty spaces or unseen characters. One way to find this out is through the function exact. You could key in =exact(lookup_value, corresponding value from table_array), if it returns a false, you know that they are not the same values. This often happens when you import a table from an external system. One of the ways to remove spaces is through Trim function. Refer an earlier post on how to trim

2. Vlookup returns a wrong corresponding value from the table_array.

The most likely cause is that you have either entered range_lookup as true or omitted it and that table_array is not sort in ascending order on column1. Either enter range_lookup as false, or sort table_array in ascending order on column1.

3. I am looking for an approximate value through vlookup with range_lookup as true. Have sorted the table_array in ascending order, yet vlookup returns #N/A. Can you figure out what could be wrong?

vlookup with range_lookup true uses the largest value in column 1 of able_array that is less than or equal to look_up value. So if look_up value is say 50 and the table array starts with 100, then vlookup would return an error #N/A

 For instance if lookup_value is 155,

vlookup(155,A2:B5,2,true) would return 2.04

4. I am carrying out a vlookup with a 10 column table_array, vlookup returns a #REF! error.

Make sure that col_index_num does not exceed 10 and you should be fine.

General observation: When the table_array is stored in a separate workbook, make sure that workbook remains in the same location as when the vlookup was created

Let me know if you have faced any other kind of problem while using vlookup.

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)

Column A is populated with serial numbers such that each region has a fresh series

Cell A3=1, Cell A4=IF(B4=B3,A3+1,1) – drag till end of column

Column C is concatenation of columns B and A

Cell C3=CONCATENATE(B3,A3) – drag till end of column

This enables us to have a one on one mapping with region and cities

Now, to generate the result table on the right

Cells – G1:J1 is manually populated with numbers 1 through 4

Cell G2=VLOOKUP(CONCATENATE($F2,G$1),$C$3:$D$18,2,FALSE) – drag till end of column and end of row

’tis done!

Alternative solution 1 using offset

To generate the table in F11:G14

Populate F11 to F14 with East, West, North, South

Here again, we would have to sort on region (column B) first

Enter G11 = IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),OFFSET($D$3,MATCH($F11,$B$3:$B$18,0)-1+(COLUMN()-COLUMN($G$1)),0,1,1),””)

Here,

column()-column($G$1) generates numbers 0,1 etc

countif – returns number of occurences of east, west, etc

offset – offsets from the reference cell D3, number of rows given by first occurence of east – provided by match function + serial number given by the column function

You could seperate each of the functions to understand how this works. If you need any help in understanding do let me know

 

Drag to the right and below to populate the entire table

’tis done again

Alternative solution 2 using index

To generate the table in F18:G21

Populate F18 to G21 with East, West, North, South

Enter G18 =IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),INDEX($D$3:$D$18,MATCH($F11,$B$3:$B$18,0)+(COLUMN()-COLUMN($G$1))))

Drag to the right and down to populate the entire table.

’tis done once again

Read Full Post »