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 READ MORE
Category: Formula
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 READ MORE
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 READ MORE
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 READ MORE
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 READ MORE
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 READ MORE
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 READ MORE
Lookup case sensitive match with EXACT function
There are many methods to lookup case sensitive match, but in this article we will discuss array function with INDEX MATCH with EXACT function. In the below table, same name Deep Singh is repeated with small case. If we apply Vlookup formula, in both cases it will return first match without considering the case sensitivity. {=INDEX(D:D,MATCH(TRUE,EXACT(F7,C:C),0),1)} In this example, you READ MORE
Vlookup multiple criteria – 3 ways to check multiple criteria
Can VLOOKUP check multiple criteria to match the value like SUMIFS or COUNTIFS ? The answer may be no, because by default the VLOOKUP function can check only one criteria for matching. But in case if you want to use VLOOKUP to match more than one conditions, this article will explain you the different ways to check Vlookup multiple criteria. READ MORE
Two way lookup in excel with vlookup match
VLOOKUP function will search for the value in a table vertically and returns the value of the column based on the column index for matching row. The column index is defined in the formula and usually it is a constant value. With the combination of VLOOKUP MATCH function we can dynamically change the column index. This will enable two dimensional READ MORE