This is a function that would search for a specific value in the first column of the table array. It would return a value that is in the same row and placed in a different column in the same table array. V in VLOOKUP means vertical, which makes it more beneficial than HLOOKUP when comparing values located in a column that is on the left of the data that the user wants to find.
Understanding VLOOKUP and its parameters
The VLOOKUP has multiple parameters that work together to find the value that the user is looking for with it. The function has a lookup value, a table of array, index's column number, and a range lookup.
Lookup Value: This is a value that a user searches for in the first column of a table's array. This can be either a reference or a value. If there was a lookup value that was smaller than the smallest value in the same first column of that table array, it would only lead to the VLOOKUP returning with the #N/A error value.
Table Array: There needs to be either two or more columns that contain data. It is possible for this to be either a range name or a range reference. Values that are in this array's first column would be searched by a lookup array. These values can be logical values, numbers, or even text.
The array is equal to both the upper- and lowercase text.
Column Index Number: This is a column number that is in the table array, which is where a matching value is required to be matched.
If it is 1, then it would return a value that is in the first column of that table's array. If it is 2, then it would return with the second column in the array; if it is 3, it will return with the third column, and so on. When it is less than 1, then it will be returned with an error value that says #Value!
However, if the number is greater than the number of columns in a table's array, then the VLOOKUP function would return with the error value that says #REF!
Range Lookup: This is the logical value that would be specified if the user wants the VLOOKUP function to either find the exact match of the value or just make an approximating match.
Vlookup function examples
A Simple VLOOKUP Function
This example is going to make it possible to know if the goal that has been set has been achieved in this example. We'd have a product that needed to achieve a specific goal within a certain time, and now we want to know how the sales have been on the market. This is where the VLOOKUP formula is going to be extremely useful.
Twice in the same Formula In this example, we are aiming to use the VLOOKUP formulas twice, and use them to know the net worth of the sales that have been made. With the combination of the two formulas, it will make it possible to find solid details that will get us the answer that we are looking for. Let us use the same data as the one on top.
This time, we have also added another piece of data to the entire sheet, which is the total income we expect from selling the whole product. It is about knowing the performance of a specific product and comparing the total earnings with the revenues.
Advanced Double VLOOKUP Formula with Additional Data
This example uses the very same data as the previous one, but this time we have also set a goal for the entire product. We have already set out how much the business would be going under the circumstances that our goal has been set. We know that we want to set a specific percentage of the entire price away from the full retail price. This discount makes it possible for us to get rid of the discount that is meant to be discounted from the retail price.
For this to be successful, we need to add a new column to the data, which would be the price. This is where the formula would be useful for getting the answer that we'd like to get. We want to know how the sales will be affected.
A simple IF and VLOOKUP
This is an example, where we are using the IF formula with the Vlookup formula for ease. We are using this to find out how the business is impacted by the decision, so we are using the IF and VLOOKUP formulas to find the answer we need. This is because we want to know how much the discount would be when it has been subtracted from the total earnings.
Combining a double VLOOKUP formula with an IF formula
This example is about finding out the percentage that it would cost us under the circumstances, which would be difficult for us to find out under other circumstances. The best way to do it is by finding out what the value would cost us with the discount that has been given to the products, due to the upcoming sales period.
As a result, we would want to know how this would have an effect on the revenues coming into the business as a result. This could be something that would help in acknowledging the business process.
Age of Sales with Four Different Formulas
In this example, we'd have different performances, and we'd want to know the age of our sales, and determine the age. This is where four different formulas are useful in the long run, and the circumstances would require a pure understanding of simultaneously using the formulas. These formulas are INT, Yearfrac, Date and Vlookup that work together to provide the result we desire.
Simultaneously Using the Vlookup Function with Two Other Functions
The example is about using the IF, ISNA and VLOOKUP formulas at the same time. This is what we need to use to find the information that shows if our aim has been achieved or not. The function is based on finding solid and appropriate information that makes it possible to easily know the answer.
Triple VLOOKUP Formula
This example is about using three different Vlookup formulas simultaneously to find the answer that we are looking for, which would make it possible and easy to find the desired answer.
VLOOKUP with Text and Number
In this example, we are going to use the VLOOKUP formula with regard to all data being in text. We are trying to use the VLOOKUP formula to find the value that we are looking for, and under these circumstances, the whole data set has been given a label to make it successful.
VLOOKUP from a Different Spreadsheet
This example is an excellent formula, under the circumstances that we have multiple years of experience in the business. The previous year was put into a Microsoft Excel spreadsheet, while we have the current year in another spreadsheet, but in the same document. This is where the VLOOKUP formula needs to be used with regard to the two spreadsheets.
Vlookup a common part
Suppose you have two columns of data. Doesn't matter what kind of data they contain. Let it be customers and employees. You want to find common part of these two columns.
Column A - name of your clients
Column B - your employees
Column C - common part of these tho collections of data.
Your task is to check if your employees are your clients as well.
Answer: Use such a formula:
Just copy that formula in C2 cell and drag it down.
Further reading: How to use Xlookup function in Excel