How to Correct VLOOKUP error (#N/A,#REF!,#VALUE!,#NAME?)

This article explains different errors which VLOOKUP result can show, reasons for the same and how to handle VLOOKUP error. In addition to this, the precaution to be taken while using VLOOKUP to get correct result is also explained. #N/A error in VLOOKUP #REF! Error in VLOOKUP#VALUE! Error in VLOOKUP#NAME? Error in VLOOKUP #N/A error in VLOOKUP #N/A error occurs when the result is not found by VLOOKUP formula. This error can happen following cases: When range_lookup is TRUE or omitted in…

Continue Reading

Remove Extra Spaces from Lookup Values: VLOOKUP with TRIM

While using VLOOKUP formula, if the data consists extra spaces , we may not get the results. VLOOKUP checks for exact match and even single extra space in data also will not give the results and will show #N/A error. But VLOOKUP is not case sensitive. In this article we will explain how to use VLOOKUP with TRIM to remove extra spaces in following situations: How to use VLOOKUP with TRIM when the lookup_value consists of extra space.How to  use…

Continue Reading

VLOOKUP column index auto update with COLUMN function

In VLOOKUP formula, column index defines the column number of table from which the data is to be retrieved in results. When we have to put VLOOKUP formula for many columns from same source table, we can use $ sign to lock the cell reference in formula. This will help us to drag the formula to all columns without editing it. But in that case column index will not be changed automatically and same result appears in all cells. You…

Continue Reading

VLOOKUP to replace excel if multiple conditions (Nested IF)

When we have to do a logical test and get the result TRUE or FALSE, we can use IF function. But in case if there are multiple logical tests to give the result, we can put nested IF formula. In a single formula we can put excel IF multiple conditions within IF function. This is called as nested IF. If the conditions are many, nested IF formula may be little complicated. Modifying or Correcting the nested IF formula are difficult.…

Continue Reading

Nested vlookup in excel : vlookp trick

Nested VLOOKUP is helpful when you have to retrieve the details from two table and the lookup value is not the common field in both the table. You may know nested IF which is helpful if you are checking multiple condition in same formula. Similarly you can use VLOOKUP formula within VLOOKUP to get lookup value. This can be explained with below example. In this example, in one table the data consists of Employee Number, Tax Account No, Name and…

Continue Reading

Speedup your vlookup: 2vlookups instead of 1 vlookup

Simple VLOOKUP formula is reasonably faster with small table data.  But in case if you are handling a large data table like 10,000 or 1,00,000 rows, VLOOKUP may be bit slower. To speedup your VLOOKUP you can use below technique which will improve the speed by more than 10 times.  We may not understand how exactly it works  in the back end, but it really works. In VLOOKUP, there are two types of matching which we will have to define…

Continue Reading

Lookup Picture in excel with Index Match

We know that the vlookup will retrieve the value from a table. But in case if we want to retrieve picture like vlookup, we have to follow below steps. Let us understand how to lookup picture in excel. Vlookup can not do picture lookup, therefore we will have to use INDEX MATCH with some more tricks. How to Lookup Picture in excel? Following are the steps to lookup picture: Prepare the data table with pictureDefine Name with INDEX MATCH function Link Picture…

Continue Reading

Excel autocorrect feature and tricks to save time

While typing in excel, you might have seen that excel will do many auto correct functions.  We can customize the autocorrect features in excel as per our requirement. To go to excel autocorrect options, follow the below steps: Click on Office buttonClick on Excel options and thenClick on Proofing Click on AutoCorrect options. It gives various options and you can customise the autocorrect feature as per your requirement.  Easy entry of repeatedly used words using excel autocorrect In case if you…

Continue Reading

How to insert drop down list in excel

In various web pages and software you might have seen drop down list.This drop down list allows us to select the input without any error. Now let's see advantages of drop down list and how to insert drop down list in Excel : it will give options to the user to select the input easily and quickly the input given by user will be accurate as per predetermined list. How to insert drop down list in excel: Direct entry to Data…

Continue Reading

How to add the developer tab to the ribbon

In case you want to learn macros, vba programming and want to become advanced user of excel, you will have to add the developer tab to the ribbon. By default in excel this will not be added in the ribbon. How to add the developer tab to the ribbon in excel 2007 ? Click on excel Office Button Click on Excel Options at the bottom of the list, it will open Excel options window Select Show Developers tab in the…

Continue Reading
Close Menu