Welcome! Enter below articles or choose one from the menu above

How to Create and Format a Pivot Table in Excel

In this lesson, you will learn how to use pivot tables in Excel.

Read more ...

How to calculate ROA?

In this Excel tutorial lesson, you will learn how to calculate ROA in Excel.

Read more ...

SUMIF Function; Ten Different Ways to Use It

SUMIF Excel is a function that effectively add all numbers that are given to cells' range, using the criteria it was given. When desiring to apply different criteria, then SUMIF function is an effective method for performing that task.

Read more ...

Calendar Template

Creating calendar on Microsoft Excel 2013 is relatively easy. There are different steps for creating it.

Read more ...

How to insert Panel Chart in Excel

The panel chart is set of similar charts that have been aligned neatly in the panel. The chart also has other names like trellis displays or small multiples. Its goal is to help the audience to understand some data that contains different variables. It makes it a lot easier to structure segment of the document that the author is explaining to the audience.

Read more ...

Vlookup Tutorial with Examples in Excel

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.

Simple VLOOKUP Function

Using VLOOKUP

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.

Usage of VLOOKUP Twice in the same Formula

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.

Advanced Double VLOOKUP Formula with Additional Data

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.

Easy IF and VLOOKUP

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.

Double VLOOKUP Formula with IF Formula

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.

Age of Sales with Four Different Formulas

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.

Simultaneously Using VLOOKUP Function with 2 other Functions

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.

Triple VLOOKUP Formula

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 with Texts and Number

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 from a Different Spreadsheet

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:

=IF(ISERROR(VLOOKUP(B2,$A$1:$A$100,1,0)),"",VLOOKUP(B2,$A$1:$A$100,1,0))

Vlookup common part of collection

Just copy that formula in C2 cell and drag it down.

Further reading:
How to use Xlookup function in Excel