VLOOKUP only looks from left to right, therefore if the Name column (C) appears after the Chemistry column (F), this function will fail. To circumvent this constraint, either copy the Name column to the left hand side or use the functions INDEX and MATCH together. VLOOKUP will always search for the first match. If there are two people with the same name, VLOOKUP will only return the first value. In the above example, if there is another individual named John, you will be unable to get his data person. VLOOKUP is not case sensitive; the names John, JOHN, and JoHn are all handled the same. If you modify the cell range by introducing or removing columns, the VLOOKUP value will return an incorrect result. If you eliminate the Physics column in the preceding example, the Math’s column will become the second column and you will obtain an incorrect result. The data you see may appear to be a number, but it may be structured as text. In this example, you used the VALUE function to convert the text to a number. If the data you’re looking for isn’t available, you’ll get a #N/A error. If the name John is not present, you will see this error. In addition, if you input the incorrect col index num, you will receive an out of reference error similar like this. V#REF!. If you enter 7 as the third parameter in the preceding example, you will receive this error because there are only four columns to retrieve data from. Before writing the Vlookup formula, make sure the data columns you’re comparing don’t have any leading and trailing spaces, as this will produce incorrect results because the data you’re seeing and the actual data may be of different lengths. Assume the name you’re looking at is John, but the data length is five because of a following or preceding space. In such instances, you must utilise the trim feature to remove the unwanted gaps.

VLOOKUP Function - TechnicalKingdom

EXCEL VLOOKUP FUNCTION

Excel VLOOKUP Function Introduction

Vlookup is a versatile and strong technique in Excel that is often used for comparing excel sheets, extract.This function searches a table or cell ranges for the value you supply (searches in the table’s first column

Description of Excel VLOOKUP Function

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Parameter list:

Lookup value – the value to search for; for example, if you wish to search for the name John in another list, you will choose the cell with the name John.
table array – the location of our search will undoubtedly be a collection of cells or a table in the same sheet, another sheet, or another workbook.
col index num – the table column number from which to get a value. [Optional] range lookup TRUE Indicates a close match (default). FALSE denotes an exact match. Right now, the parameters appear to be complicated, but don’t worry, as I explain them with examples, you’ll see how simple they are.

Step By Step Guide of VLOOKUP Function

Example :

In this example, we wish to find out how much each student earned first, we will search for the name John in the cell range C7:F10, thus we will put the name John as the first argument in double quotes, as if we were searching a text, and the cell range C7:F10 as the second parameter.
The third argument specifies the column index number from the cell range C7:F10, where C is the first column number, D is the second, E is the third, and to get an exact match, we provide 0 or False in the fourth argument.
The important thing to remember here is that in the cell range C7:F10, the C column (first column) must have the value John for which we are searching; otherwise, VLOOKUP will return a #N/A error.
In this example, we are hardcoding the value we are looking for, which is not an efficient method. In the second example, we will make some changes to make things simpler.

Key Points

VLOOKUP only looks from left to right, therefore if the Name column (C) appears after the Chemistry column (F), this function will fail. To circumvent this constraint, either copy the Name column to the left hand side or use the functions INDEX and MATCH together.
VLOOKUP will always search for the first match. If there are two people with the same name, VLOOKUP will only return the first value. In the above example, if there is another individual named John, you will be unable to get his data person.
VLOOKUP is not case sensitive; the names John, JOHN, and JoHn are all handled the same. If you modify the cell range by introducing or removing columns, the VLOOKUP value will return an incorrect result. If you eliminate the Physics column in the preceding example, the Math’s column will become the second column and you will obtain an incorrect result. The data you see may appear to be a number, but it may be structured as text.
In this example, you used the VALUE function to convert the text to a number. If the data you’re looking for isn’t available, you’ll get a #N/A error. If the name John is not present, you will see this error. In addition, if you input the incorrect col index num, you will receive an out of reference error similar like this. V#REF!. If you enter 7 as the third parameter in the preceding example, you will receive this error because there are only four columns to retrieve data from.
Before writing the Vlookup formula, make sure the data columns you’re comparing don’t have any leading and trailing spaces, as this will produce incorrect results because the data you’re seeing and the actual data may be of different lengths. Assume the name you’re looking at is John, but the data length is five because of a following or preceding space. In such instances, you must utilise the trim feature to remove the unwanted gaps.

EXCEL VLOOKUP FUNCTION

Excel VLOOKUP Function Introduction

Description of Excel VLOOKUP Function

Step By Step Guide of VLOOKUP Function

Key Points

Excel VLOOKUP Function Template/Exercise

Related Functions

HLOOKUP Function

HLOOKUP Function

VLOOKUP Function

VLOOKUP Function

INDEX Function

INDEX Function

MATCH Function

MATCH Function

DATE Function

DATE Function

IF Function

IF Function

OR Function

OR Function

AND Function

AND Function

RANK Function

RANK Function

SUMIF Function

SUMIF Function

XIRR Function

XIRR Function

WORKDAYS Function

WORKDAYS Function

Excel Functions

Excel Formulas

Excel Shortcuts