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…)

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

Sat 30 May 2015

Excel Quiz 13 - LOOKUPs Quiz - Part I

By |Saturday, May 30th, 2015|Categories: Quizzes|Tags: , , , , , , , |3 Comments

The thirteenth quiz in the series to test your knowledge on Lookups in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

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.