How Does Excel’s VLOOKUP work?
When looking up a specific piece of information in a table or data set, Excel’s VLOOKUP Function[1] can be used to extract the relevant data or information.
The VLOOKUP function instructs Excel too, in plain English, “Look for this item of information (for example, bananas) in this data set (a table), and tell me some corresponding information about it (for example, the price of bananas)”.
Formula VLOOKUP
=VLOOKUP(lookup value, table array, col index number, and [range lookup])
The formula is telling you to look for this piece of information in the area below and then provide me with some data from another column that corresponds to it, to put it simply in English.
Following are the arguments used by the VLOOKUP function:
Lookup value (required argument) – Lookup value identifies the value in the first column of a table that we want to look up.
Table array is the data array that needs to be searched, and it is a required argument. The array’s leftmost column is where the VLOOKUP function looks.
Col index num (required argument) – This is an integer that specifies which column in the table array you are providing you want to return a value from.
If the function cannot find an exact match to the lookup value, the range lookup (optional argument) specifies what it should return. If the argument is set to TRUE or FALSE, then:
TRUE – Approximate match, which means that in the absence of an exact match, use the next-closest match listed below the lookup value.
FALSE – Exact match, which means it will give an error if no exact match is found.
How to Use VLOOKUP in Excel
Step 1: Organize the Data
To use the VLOOKUP function effectively, you must first make sure that your data is well-organized and appropriate for the function.
The information you want to look up must be to the left of the corresponding data you want to extract because VLOOKUP operates in a left-to-right fashion.
You can see from the aforementioned VLOOKUP example that the “good table” can quickly execute the function to look up “Bananas” and return their price because Bananas are in the leftmost column. You can see that the “bad table” example has an error message because the columns are not in the correct order.
Because of this, it is strongly advised to use INDEX MATCH rather than VLOOKUP, as it is one of the major drawbacks of VLOOKUP.
Also Read: How to Password Protect an Excel File: Step by Step Guide to Secure a File
Step 2: Tell the Function What to Lookup
This step instructs Excel on what to look for. We begin by entering the formula “=VLOOKUP(” and then choose the cell containing the data we wish to look up. In this instance, it’s the cell that has “Bananas” in it.
Step 3: Tell the Function Where to Look
In this step, we choose the table containing the data and instruct Excel to look in the leftmost column for the data we chose in the previous phase.
In this instance, for instance, we highlight the entire table from column A through column C. Excel will search for the data we instructed it to seek up in column A.
Step 4: Tell Excel What Column to Output the Data From
This step involves telling Excel which column contains the information we wish to have come out of the VLOOKUP. In order to do this, Excel requires a number that corresponds to the table’s column number.
In our case, the third column in the table contains the output data, thus we enter the number “3” in the formula.
Step 5: Exact or Approximate Match
By inserting “True” or “False” in the formula, you can now inform Excel whether you’re looking for an exact match or a close one.
We enter “FALSE” in the formula because we want an exact match (“Bananas”) in our VLOOKUP example. We would get a close match if we supplied “TRUE” as a parameter instead.
When looking up an exact number that might not be present in the table, such as the number 2.9585, it might be helpful to find an approximate match. Even though 2.9585 is not included in the dataset in this instance, Excel will search for the closest number. The VLOOKUP formula will function more accurately as a result.
Our Free Excel Crash Course will teach you how to do this step-by-step.
VLOOKUP In Financial Research and Modeling
In order to make models more dynamic and incorporate many scenarios, VLOOKUP formulas are frequently employed in financial modeling and other sorts of financial analysis.
Consider, for illustration, a financial model with a debt schedule and three possible interest rate scenarios for the business: 3.0%, 4.0%, and 5.0%.
A VLOOKUP might find a low, medium, or high scenario and output the appropriate interest rate into the financial model.
The aforementioned example demonstrates how an analyst can choose the desired scenario and have the relevant interest rate flow into the model from the VLOOKUP algorithm.
Things to Remember About the VLOOKUP Function
An important collection of Excel VLOOKUP Function reminders is provided below:
When range lookup is absent, the VLOOKUP function will accept a non-exact match but, if one is present, will use it.
The function’s major drawback is that it always appears correct. It will pull information from the table’s columns to the right of the first column.
VLOOKUP will only match the first value in the case of duplicate entries in the lookup column.
The method does not take the case into account.
Let’s say a worksheet already contains a VLOOKUP formula. In that case, adding a column to the table can cause formulas to fail. This is the case because adding or removing columns does not automatically affect the values of hard-coded column indexes.
Asterisks (*) and question marks (?) are examples of wildcards that can be used with VLOOKUP.
Let’s say the function we are using in the table contains text-based numbers. It is irrelevant if all we are doing is retrieving text representations of numbers from a table column.
However, if the first column of the database has text-based numbers in it, we will get a #N/A! error if the lookup result is not likewise in text.
If the specified lookup value is not matched by the VLOOKUP function, the #N/A! error is returned.
The col index num argument exceeds the number of columns in the supplied table array, resulting in a #REF! error; or
The formula tried to refer to cells that aren’t there.
If the col index num input is less than 1 or isn’t recognized as a numeric number, the #VALUE! the error will appear.
There is no recognition of the range lookup argument as either TRUE or FALSE in the logical sense.
For More Information Visit Our Site: https://www.techllog.com/
