Sat 06 May 2017

Downloads 15 - Excel Formulas Bible

By |Saturday, May 06th, 2017|Categories: Downloads|Tags: , , , , , , , , , , , , |0 Comments

This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users.

Download it from Excel - Formulas Bible

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 12 Sep 2015

Article 25 - Reverse FIND / SEARCH & MID Function

By |Saturday, September 12th, 2015|Categories: Articles|Tags: , , , , , , |0 Comments

Excel doesn't offer any function for reverse FIND / SEARCH & MID function. VBA does offer a function INSTRREV but this article is for non-VBA folks or folks who do not want to use VBA in their spreadsheet.

Hence, the only option before us is to build them through formulas. Let's build a reverse FIND / SEARCH function.

Note - There are two differences between FIND and SEARCH which you must make a note of.

(more…)

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.