6 Reasons why VLOOKUP is not working

VLOOKUP is not working !!! 

Don’t need to get panic. I have seen many persons complaining VLOOKUP not working or VLOOKUP not working with numbers or VLOOKUP not working properly or VLOOKUP not working between sheets or VLOOKUP not working for some cells etc.

You have tried a lot to fix it but all in vain. 

Calm down, there are very few reasons behind not working of VLOOKUP function.

In this section I will show you step by step how to detect where is the problem for not working of VLOOKUP. 

But before going I would like to review the SYNTAX of VLOOKUP once again.
=VLOOKUP( lookup_value, table or array, col_index_number, [range_lookup] ) 

Below is the few Check List to check why VLOOKUP is not working

1. Not Having 'Lookup_Value' in First Column :

If there is no LOOKUP VALUE (which you are searching for, the first parameter) is not available in the table or array from where you are getting or matching data, VLOOKUP will not work. It will display #N/A error.

Please watch carefully the below image. Here I want to match the employee column in the second table in the small table from the large table to get Sales Quantity of the given employees. Here I am getting #N/A error in the first row because “Employee 14” is not present in the first table.

This is a small example, therefore, what you need to do is search manually for the Lookup Value by pressing Ctrl+F in your Keyboard.

If Value is not found in the first table, then it’s OK, use IFERROR to mask the #N/A error.

2. Counting the Wrong Number of Columns for Col_index_number

Please recount the COLUMNS count from where you are fetching data, the first table

In this example I wrongly count the column number as 4 instead of  3 
=VLOOKUP(F4,B3:D14,4,0)

3. Extra Space or Characters

Please check for Extra spaces in your formula, it you have doubts use TRIM function to remove extra spaces. To learn more about TRIM click this.

4. Not Using FALSE for Exact Matching [Range_Lookup]

Although, sometimes VLOOKUP works perfectly but do not forget to use [Range_Lookup], False or 0 for exact matching and True or 1 for approximate matching. 

Its always better to make a good habit.

5. Forgetting Absolute References (F4) When Copying the Formula

While copy and paste VLOOKUP formula or just dragging the VLOOKUP formula you must put $ sign to restrict the column not to move down or side by side. This is called Absolute reference. 

There are two ways to do this, one you insert a $ sign manually or you can use F4 (Function Key) key. 

6. A new column has been inserted in the old table

In a previously calculated table where VLOOKUP has been used which was working, inserting a new column will create VLOKKUP unstable. 

To resolve this either you can recheck your VLOOKUP formula or you can use an additional function MATCH to be nested with VLOOKUP. Using MATCH with VLOOKUP will make your formula very dynamic.

Please click here to learn how to use MATCH function with VLOOKUP.


Hope your problem is resolved now. If not please mention your problem in the comment box.

Thank you for reading... 

AVERAGEIF

Hi Friends,

In my last article, I discussed about AVERAGE function in Microsoft Excel, a very basic statistical function (arithmetic mean).

But what if, if I need the average of some given numbers if some condition is met. Let me explain it with an example. 

Suppose I need the average of all the Sales and Target values of South zone from the data provided where North, East West and South zone’s figures are plotted one after another in consecutive rows (as given in the below picture).

Here I have two options, one, using IF function nested with Average or I need some special Function which will allow me to average out those numbers where one given condition met.Today, in this article, I will discuss about the built-in function in Excel which will provide me the average value of some number if certain condition is met. The function is AVERAGEIF.

Let us discuss about it today…

Description

The Microsoft Excel AVERAGEIF function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria.

The AVERAGEIF function is a built-in function in Excel that is categorized as a Statistical Function. The AVERAGEIF function can be entered as part of a formula in a cell of a worksheet.

Later, I will show you how to use this function nested with other functions.

Syntax

The syntax for the AVERAGEIF function in Microsoft Excel is very similar to SUMIF. If you want to know how to use SUMIF, please click in this link.

=AVERAGEIF(range, criteria, [average_range] )

Parameters or Arguments

range

The range of cells that you want to apply the criteria against.

criteria

The criteria used to determine which cells to average.

Note : If the Criteria is in text or if you are using any operator please use Double Quotes.

average_range

Optional. It is the cells to average. If average_range is omitted, it uses range as the value for this parameter.

Returns

The AVERAGEIF function returns a numeric value.

Example 1 :

How to use AVERAGEIF with TEXT/STRING data type ?

In the below image, I want to get the Average of the Sales & Target value of South Zone only. The formula for this is

=AVERAGEIF(B2:C15,"South",C2:C15)            For Target portion

=AVERAGEIF(B2:C15,"South",D2:D15)            For Sales portion

Please note I mentioned South within double quotes.

How to use AVERAGEIF with TEXT or STRING as Criteria

Example 2 :

How to use AVERAGEIF with OPERATOR like =, <, > etc. ?

In the same example, let suppose I need the AVERAGE of sales value where TARGET value is Greater than 150.

Note : Here the rage I provided to get the average is D2:E15, because I want my AVERAGE function to search for 150 in the Target column which is Column D and the average will come from the next column E which is the Sales column.

The below image will make it very clear.

How to use AVERAGEIF with OPERATORS as Criteria

Hope this article is enough for you to understand the AVERAGEIF function. If any question arises, feel free to comment in the comment box below.

Thanks for reading…

AVERAGE

Hi Friends,

Till now I had discussed about the Logical, Textual & Mathematical and more functions in Microsoft Excel. But as you aware that data analysis is practically based upon statistical calculations (factual data to predict or monitor the future behavioral pattern of an organization), may it be a very common or complex calculations.

Therefore, friends, in my next few articles, I will discuss about few Statistical functions available in Excel which are commonly used in daily data analysis.

Later, I will discuss about more complex statistical functions. Let’s start from the very basic statistical concepts.

Mean, Median, Mode, Variance, Standard Deviation etc., are the common statistical methods which we normally use in Data Analysis.

Today we will discuss about the AVERAGE function which is also known as arithmetic mean. AVERAGE of few number is sum of the numbers divided by the count of the numbers. I hope we all know this basic methodology to calculate AVERAGE.

Now let see how we can derive AVERAGE of few numbers in Excel, using Excel’s built in function.

The function name is very easy to remember and very easy and simple to implement.

Syntax

The syntax for the AVERAGE function in Microsoft Excel is

=AVERAGE( number1, [number2], ... [number_n] )

Parameters or Arguments

number1, number2, ... number_n

Numeric values which can be numbers, named ranges, arrays, or references to numbers. 

I will discuss about this function (AVERAGE) and other functions (Mathematical) to show you how to use ARRAY in these functions in upcoming articles.

Returns

The AVERAGE function returns a numeric value.

Example :

How to use AVERAGE function in Excel

In the above image you can notice that I entered the formula as =AVERAGE(D3:D6).  I did by typing =AVERAGE ( and then I selected the cell range for which I need to get the average i.e., D3 to D6.

Alternatively you can type =AVERAGE( and the press the Control button of your computer and click on the cells you need to get the average as shown in the below image.

How to use AVERAGE function in Excel

Hope this article is enough for you to understand the AVERAGE function. If any question arises, feel free to comment in the comment box below.

Thanks for reading…

IFERROR Function

In my previous article, I discussed about the different types of errors we get in Microsoft excel while working with numbers, text etc. These errors we get in Excel maybe it is #N/A or #VALUE! or #REF! (or #DIV/0! or #NUM! or #NAME? or #NULL and so on) is very irritating while analyzing data because it will not allow you to perform basic operations like SUM, SUMIF or SUBTOTAL etc. (it will show errors).

Therefore, Excel has given us one function named IFERROR to overcome such situations. 

Today I will discuss about IFERROR function with few examples.

The IFERROR function is a built-in function in Excel that is categorized as a Logical Function. 

Syntax

The syntax for the IFERROR function in Microsoft Excel is:

=IFERROR(value, value_if_true)

Parameters or Arguments

value

The ‘value’ may be a formula or simply division or anything which has an outcome.

value_if_true 

The alternate value that is returned if the formula results in an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL). Otherwise, the function will return the result of the formula if no error occurs.

If you remember the functionality of IF function, then it will be very easy to understand. ‘value_if_true’ is the outcome you want to display or printed IF THERE IS ANY ERROR in the ‘value’ parameter.

Let us have two examples and I hope the functionality of this function will be very clear.

Examples :

In the below image, I want to show you a very small calculation on Target vs Achievement in percentage, it is Achievement divided by Target. But you know that whenever I want to divide some value with a Zero it will return an undefined value as output (which is the basic rule in mathematics) and in Excel it is division error (#DIV/0!).

How to use IFERROR Function in Excel

Similarly, whenever I try to divide some numeric value by some Text, it will also report me an error in Excel as #VALUE!.
Now, if I use IFERROR Function the problem will disappear. What I do in my daily data analysis for very long formula is that I first type the formula and then I move at the beginning of the formula after ‘=’ sign, type IFERROR and one opening bracket and then I move to the end of the formula and type a comma and usually I put a ‘0’.

In place of zero you can use anything you wish, like ‘OUTPUT-ERROR’ etc.

Note : If you are comfortable using VLOOKUP by typing at the formula bar or within the Cell, you can start typing like “=IFERROR(VLOOKUP(…….),0)”

In the below example, I will show you how you can use IFERROR function in VLOOKUP.

How to use IFERROR Function with VLOOKUP Function in Excel

There are two tables, one is TABLE – 1 which is the data provider, and another is TABLE – 2, data to be mapped here from TABLE – 1.

If you check carefully at TABLE – 1, you will notice there are four names A, B, C & D whereas in the TABLE -2 name field contains names as A, X, P & D.

In this situation whenever I try to map TABLE - 2 using VLOOKUP the X & P will display #N/A errors because of unavailability of the data. But if I use IFERROR function before the VLOOKUP function it will replace the #N/A by the value I need (here I opted for “VALUE NOT FOUND”)

Hope this article is enough for you to understand the IFERROR function. If any question arises, feel free to comment in the comment box below.

Thanks for reading…

Now Function

In this article, I will discuss about a very tiny but very powerful function in the category Date/Time Function and it is NOW Function.

Although it is a very small function in Microsoft Excel but you can list this function in your time saving function lists.
So, let’s see how to use this useful function.

Description
The Microsoft Excel NOW function returns the current system date and time.

The NOW function will refresh the date/time value whenever the worksheet recalculates which means it will refresh the date & time wherever you type something in the excel sheet. 

Note : To stop this feature of NOW Function go to Formula menu, at the end you will find Calculation tab, click on Calculation Option, select Manual from Automatic. This will prevent entire Excel from refreshing or Calculating, therefore you other formula may not work, so after you are done don’t FORGET to switch this option from Manual to Automatic.

Calculation Option

The spreadsheet will continue to display this value, until the worksheet recalculates. By pressing the F9 key, you can force the worksheet to recalculate and the NOW function will update its value.

Syntax

The syntax for the NOW function in Microsoft Excel is as under.

=NOW()

Parameters or Arguments

There are no parameters or arguments for the NOW function.

Returns

The NOW function returns a date & value.

Example :

How to use Now Function

Hope you have enjoyed this article...
Thank you for reading...