Microsoft Excel is an incredibly effective tool for managing data. It makes it simple for users to evaluate and interpret data. Microsoft Excel is unquestionably a strong and very popular programme utilized by practically every organization. Excel offers a wide variety of features that make working with data easier. One such Excel function is VLOOKUP.
VLOOKUP serves as a search tool by scanning vertically over a table or spreadsheet for particular data. Let’s examine VLOOKUP in more detail.
What is VLOOKUP in Excel?
VLOOKUP in Excel stands for Vertical Lookup. VLOOKUP is a built-in Excel function that, as its name implies, enables you to find specific data by looking for it vertically across the page. It may appear difficult at first, but if you give it a try, you’ll see that it’s a simple to use and very helpful tool. It is arguably one of the most well-known features, for good and bad reasons. Let us now talk about some pros and cons of using VLOOKUP.
PROS AND CONS OF VLOOKUP:
On the plus side, VLOOKUP is simple to use and performs a highly valuable task. VLOOKUP can scan a table, locate a match, and deliver the right result, especially for beginners. Success with VLOOKUP is a must for moving from a novice Excel user to a proficient one.
The drawback of VLOOKUP is that it requires a complete table with lookup values in the first column, unlike INDEX, MATCH, or XLOOKUP. Because of this, using VLOOKUP with multiple criteria is challenging. Additionally, it’s simple to obtain false results because of the default matching behaviour. Understanding the fundamentals is the key to using VLOOKUP effectively.
VLOOKUP Formula
VLOOKUP formula or the syntax is as follows:
=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])
where,
lookup_value – The value to search for in a table’s first column.
table_array – The table from which a value should be retrieved.
column_index_num – The table column from which a value should be retrieved.
range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
Clearly, there are two possibilities if you want to be more specific:
- TRUE to find an approximate match.
- FALSE to search for an exact match
In the absence of a particular command, TRUE is the default value.
How To Find an Exact Match Using VLOOKUP?
When utilizing VLOOKUP, an exact match is typically used, especially when the lookup value is a singular value. In this VLOOKUP example, an exact match is used because the lookup value is present in the table’s leftmost column.
This time, the table array A:F contains the value 178462 or H2 in the leftmost column. Since the fourth column of the table contains the relevant value for the lookup, we enter 4 to instruct Excel to return the value from the fourth column of the table array in the same row. To tell Excel to return an exact match, we put FALSE as the fourth option.
Excel returns Ahmedabad, which matches Client ID 178462 accurately.
Excel will return #N/A to indicate that the lookup value could not be located if it is not included in the table’s leftmost column.
How To Find an Approximate Match Using VLOOKUP?
In order to discover the closest value that is less than the lookup value that we specify, Approximate Match searches for the next greatest value. Excel presumes that a lookup refers to an approximate match when no instruction is provided. Although it is generally less frequently used than exact match, it is nonetheless helpful when the lookup value is not present in the table array.
For example, the table’s leftmost column does not include information about the 10,00,100
salary. When asking Excel to provide an approximate match in this scenario, TRUE is helpful. Because 10,00,100 is not available, it will return a figure lower than 10,00,100 (10,00,000in this case), which is 145301.
Excel will return #N/A if we use FALSE (exact match in this case), as there is no value of 10,00,100 present in the leftmost column.
How to Use VLOOKUP for Multiple Criteria?
The fundamental principle of utilizing VLOOKUP in a situation with multiple criteria is to concatenate the various criteria so that you can utilize them as a single lookup value. In this case, the ampersand (&) will be used for concatenation. Therefore, before continuing with the lookup, we must create a helper column that joins the “Table_arrays.”
First, create a helper column. For this, let’s take an example. We will create a new column to combine the information from the “Name” and “City” columns. Insert a column after B which is the Helper Column (name it as per choice). Copy the formula (=A2&B2) to the remaining rows of the data after entering it in cell C2 of the worksheet.
Now let’s follow these steps to understand the multiple criteria function better.
- To extract the data, insert the VLOOKUP function in the empty cell.
2. Enter the first argument which is “lookup_value”. This is the thing that you already know. Here “Name” and “City” is known while “Client ID” is not known.
To alter the lookup value in the function, create an Excel VLOOKUP multiple criteria.
The two criteria to include in the VLOOKUP should be concatenated to form the lookup value. In G2, H2, and I2, respectively, place the VLOOKUP formula, the first criterion, and the second criterion. H2&I2 should be the lookup value for the VLOOKUP.
3. Enter the second argument which is “table_array”. The place where you are seeking the data is the “table array.” It would be columns A through E if you were using a “regular” VLOOKUP. Columns C through E must be used in this scenario due to the assistance column.
4. Enter the third argument which is “col_index_number”. The column from which you want to return the result is indicated by the ‘col index number’. Our “table array” consists of three columns, and we want to return the Client ID, which is in column E (the third column). In our example, this number should be 3.
5. Enter the fourth argument which is “[range_lookup]”. Simply a “TRUE” or “FALSE” tells Excel whether we want to employ an approximate or exact match for our query with the [range lookup] field. For this syntax argument, we just type “FALSE” in this example.
Press enter and you’ll get the desired result i.e. the Client ID.
Conclusion
One of the most frequently used Excel functions is VLOOKUP. Based on a lookup value in the leftmost column, it looks through a specified data collection and returns a corresponding value in the same row.
When a unique known identifier is the lookup value, an exact match is typically utilized to return the exact corresponding data in the same row. An approximate match is sometimes used to discover the closest match given the criteria when the lookup value is known.
VLOOKUP is a highly helpful tool when it comes to looking for information while working with numerous data sets, despite the fact that it initially appears to be tough to set up. It’s critical to understand that VLOOKUP only finds matches by scanning to the right of the data and only returns the first match.
Leave a Reply