Sat 08 Apr 2017

Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted

By |Saturday, April 08th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be lelia.vang@gmail.com as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.

(more…)

Sat 16 Jul 2016

Article 41 - Findings from the VLOOKUP and INDEX/MATCH shootout

By |Saturday, July 16th, 2016|Categories: Articles|Tags: , , , , , , , , , |0 Comments

Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time.

Following is the methodology to test -

1. The worksheet 100000 contains 100001 records. Column A is Numbers and column B is Text. Column C is alpha-numeric which is the result field.

(more…)

Sun 27 Dec 2015

Excel Quiz 28 - LOOKUPs - Part II

By |Sunday, December 27th, 2015|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 28 - LOOKUPs - Part II

Part II quiz on LOOKUPs in Excel

Fri 19 Jun 2015

Article 16 – Dependent Data List 02 – Creating Flexible / Dynamic Dependent Data List even First Drop Down is Flexible

By |Friday, June 19th, 2015|Categories: Articles|Tags: , , , , , |0 Comments

--- The Excel file related to this article can be downloaded from Dependent Drop Down List 02 ---

In Article 15, we saw that our Dependent Data list can grow / reduce vertically but we had fixed the horizontal range. Now, we will remove this restriction and our data list can grow / reduce vertically as well as horizontally.

1. Prepare the formula for first drop down.

In article 15, we saw that it is very simple i.e. $F$1:$K$1. Let's assume that maximum it can grow till Z1. In this case, we need to prepare a formula which will give us a range of $F$1:$K$1 within the range of $F$1:$Z$1 if South America is selected. If another entry is added or deleted, it should adjust appropriately.

The formula in this case would be -

=OFFSET($F$1,0,0,1,COUNTA($F$1:$Z$1))

(Though OFFSET is a volatile formula but simplicity of OFFSET is a reason to use)

2. Prepare the formula for second drop down

The formula was already there, only change is that $K is replaced by $Z. (As I said earlier 12 is a number till which the list can grow. You can put any number say 100, 900 etc.)

=OFFSET(INDEX($F$1:$Z$12,,MATCH($A$2,$F$1:$Z$1,0)),1,0,
COUNTA(INDEX($F$2:$Z$12,,MATCH($A$2,$F$1:$Z$1,0))))

3. Create the first drop down list and second drop down list utilizing above formulas.

Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET($F$1,0,0,1,COUNTA($F$1:$Z$1))

Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET(INDEX($F$1:$Z$12,,MATCH($A$2,$F$1:$Z$1,0)),1,0,
COUNTA(INDEX($F$2:$Z$12,,MATCH($A$2,$F$1:$Z$1,0))))

Now, your dynamic drop down list which can grow vertically as well as horizontally is ready.

Sat 06 Jun 2015

Article 15 - Dependent Data List 01 - Creating Flexible / Dynamic Dependent Data List

By |Saturday, June 06th, 2015|Categories: Articles|Tags: , , , , , , , |0 Comments

---- Excel file related to this article can be downloaded from Dependent Drop Down List ----

In this article, I want to create a dynamic dependent data list. It means that entries can be added, removed at any point of time without impacting your Dependent Data List. Without making matter complicated, this article assumes that first level is fixed and second level can keep changing. In second article of this series, I am going to remove this dependency also, hence both first and second level of entries can be removed / added in second article. The examples which I am going to show is one level of dependency but same logic can be extended to any level of dependencies.

1. Create the right layout for your dependent data list

Suppose, you have a list of 6 continents and you want to display the countries on the basis of continents selected. The step 1 is to layout your data properly.

Below, is the layout which you need to use. First row will contain the name of continents (i.e. first level drop down values) and below them will be the list of countries (i.e. second level of drop down values)

No blanks are allowed in between the list entries. Blanks can be only at the end.

Dependent Drop Down Layout

2. Create the first drop down

Let's assume that cell A1 needs to contain the first drop down.

Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put $F$1:$K$1

First Dropdown

3. Prepare the formula for second drop down

Now, it can be simplified to finding the range which contains first drop down value in  first row. For example, if I select South America, I should be able to get the range $K$2:$K$12 (I have taken $K$12 for illustration purpose only. You can have any range. The trick will be to show only the values from $K$2:$K$6 in case of South America, $H$2:$H$5 in case of Australia and so on)

The range can be located by INDEX - MATCH combination as shown below. Notice, there are two commas after $K$12 which indicates that all rows should be selected for the column in the range $F$2:$K$12

=INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))

This formula gives a range of $K$2:$K$12 for South America.

4. Create the second drop down

Let's assume that we want to create the second drop down in B1.

Select B1 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))

Now, select a continent name in A1 and you will have list of countries shown in dependent drop down.

Second Dropdown with Blanks

5. Remove the blanks from second dropdown

We notice that there are blanks in second drop down at the end of the list. We need to remove these.

The logic, we will adopt is this - We have got the range in step 3. Now we need to count the non blank cells in that range and use OFFSET to get the required range.

=OFFSET(INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)),1,0,
COUNTA(INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))))

Note - INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)) is first part of OFFSET and part within COUNTA making matter very simple. Hence, the formula is OFFSET("index formula",1,0,COUNTA("index formula")). In first INDEX, in place of $F$1:$K$12 we can put $F$1:$K$1 also. But as I said, I want to keep uniformity and make matter simple by having a very simple formula OFFSET("index formula",1,0,counta("index formula")).

INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)) - Gives $K$1 in case of South America, $G$1 in case of Asia and so on. But, our range needs to start from second row i.e. $K$2 in case of South America, $G$2 in case of Asia and so on. Notice 1 in bold within OFFSET, it shifts the reference by 1 row. Hence, it makes $K$1 to $K$2 and so on.

COUNTA(INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))) - Gives number of non blank cells. In case of Asia, it is 6. Hence, our reference becomes $G$2:$G$6.

6. Put the above formula in Dependent Data List.

Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET(INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)),1,
0,COUNTA(INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))))

Now, you have perfect looking dependent data list.

Second Dropdown without Blanks

Sat 31 Jan 2015

Article 10 - Intersection Operator in Excel

By |Saturday, January 31st, 2015|Categories: Articles|Tags: , , , , |0 Comments

Today, I am going to talk about a nifty but not so well known feature of Excel. This is about Intersection Operator in Excel. Before, I go deep in Intersection Operator, I would like to talk about Reference Operators in Excel. There are 3 Reference Operators -

1. Range Operator (represented by Colon) - It specifies a range. Hence, A1:B10 where a colon (:) has been used, specifies all cells contained between A1 to B10. Similarly, D:D specifies entire column D. 3:3 specifies entire row 3.

2. Union Operator (represented by Comma) - It specifies the union of ranges. Union means inclusive of all cells in the ranges specified. Hence, if you specify A1:A5,D2:D4 it will include all cells lying between A1 to A5 and D2 to D4. Hence, if I specify =SUM(A1:A5,D2:D4)
The answer would be 221.
If I specify =SUM(A1:A5,B2,D2:D4,C4,C1:C2), it will pick up all cells from A1 to A5, B2, D2 to D4, C4 and C1 to C2.
The answer would be 326.

(more…)

Sat 10 Jan 2015

Article 9 - Overcome WildCard VLOOKUP / MATCH Problem when Target String is more than 255 Characters

By |Saturday, January 10th, 2015|Categories: Articles|Tags: , , , , |2 Comments

Once again, I have got idea to write this article after I responded to this post Vlookup to find URL using wildcard in Excel Microsoft Community.

Before I delve into problem statement, I want to start with some basic information. Maximum column width can be of 255 characters, this means that if I select a column, take right click and select Column Width, I can give a maximum value of 255. If I try to give more than 255, it will immediately give an error message.

Length of cell contents (text) - 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

(more…)

Sat 06 Sep 2014

Tips & Tricks 48 - Multi Column VLOOKUP

By |Saturday, September 06th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You know VLOOKUP, one of the most loved function of Excel. The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Here look_value can be a single value not multiple values.

Now, you are having a situation where you want to do vlookup with more than 1 values. For the purpose of illustrating the concept, let's say we have 2 values to be looked up.

Below is your lookup table and you want to look up for Emp - H and Gender - F for Age.

1

=INDEX(C2:C12,MATCH(1,INDEX(--((A2:A12=F2)*(B2:B12=G2)*(ROW(A2:A12)-ROW(A2)+1)<>0),,),0))

Concatenation Approach

=INDEX(C2:C10,MATCH(F2&"@@@"&G2,INDEX(A2:A10&"@@@"&B2:B10,,),0))

@@@ can be replaced by any characters which should not be part of those columns.

By concatenation, you can have as many columns as possible.

CAUTION - Result of entire concatenation should not be having length more than 255. Hence, F2&"@@@"&G2 should not have more than 255 characters.

Another alternative is to use below Array formula -

=INDEX(C2:C12,MATCH(1,--NOT(ISLOGICAL(IF(A2:A12=F2,IF(B2:B12=G2,C2:C12)))),0))

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

Sat 14 Jun 2014

Tips & Tricks 15 - Most Frequently Occurring Value in a Range

By |Saturday, June 14th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Assuming, your range is A1:A10, enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))

The non-Array version of above formula

=INDEX(A1:A10,MATCH(MAX(INDEX(COUNTIF(A1:A10,A1:A10),,)),INDEX(COUNTIF(A1:A10,A1:A10),,),0))