This short tutorial uses Excel and sample file to show you a working Download the Excel VLOOKUP sample file shown in the More. VLOOKUP Formula In Excel with Example PDF. Microsoft Office (MS) Excel has large number of functions and utilities which an average user. Examples. VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup ]). What value are you searching for? This is the lookup value. Excel will look.

Author: | BRICE COOKSLEY |

Language: | English, Spanish, French |

Country: | Belize |

Genre: | Lifestyle |

Pages: | 151 |

Published (Last): | 31.03.2016 |

ISBN: | 590-8-18528-857-7 |

Distribution: | Free* [*Register to download] |

Uploaded by: | JACINDA |

VLOOKUP is the most popular Excel function. Here are 10 VLOOKUP Examples that will make you a VLOOKUP pro + a FREE VLOOKUP Ebook. General Ledger. VLookup. When you use a lookup function in Excel, you . In this example, row two of the array is also row 2 of the worksheet. In this tutorial, you learn how to use VLOOKUP in Excel in 6 (easy) steps. BONUS: Download the VLOOKUP Excel Workbook Sample File (with 2 pre- loaded .. That's why I've compiled this tutorial into a neat page PDF.

VLOOKUP function is best suited for situations when you are looking for a matching data point in a column, and when the matching data point is found, you go to the right in that row and fetch a value from a cell which is a specified number of columns to the right. VLOOKUP function looks for a specified value in a column in the above example, it was your name and when it finds the specified match, it returns a value in the same row the marks you obtained. Here are 10 useful exampels of using Excel Vlookup that will show you how to use it in your day-to-day work. From the above data, I need to know how much Brad scored in Math. First, it looks for the value Brad in the left-most column. It goes from top to bottom and finds the value in cell A6. As soon as it finds the value, it goes to the right in the second column and fetches the value in it. You can also use a cell reference that contains the lookup value. In Example 1 above, we hard-coded the column value. Hence, the formula would always return the score for Math as we have used 2 as the column index number. For example, as shown below, you can change either the student name or the subject name, and the VLOOKUP formula fetches the correct score. To make this two-way lookup formula, you need to make the column dynamic as well. So when a user changes the subject, the formula automatically picks the correct column 2 in the case of Math, 3 in the case of Physics, as so on.. Hence, if you use Math, it would return 2 as Math is found in B2 which is the second cell in the specified array range.

So its use can be done to see the value inside of any cell of column. Keep remember that lookup value should always in first column of range so that it can work better.

VLOOKUP is designed to retrieve the organised data from vertical rows of table where each row represents a new record. We will try to understand by one sample problem.

Like we cannot retrieve the name from the above table. Because we are getting the Salary based on the Id but Name is not right side of Id column.

The first column of values in the table is column 1. After pressing OK. You will find the salary of Id as If you want to find the salary of any employee further, just change the with the desired Id.

You will find the salary of that desired number. Find the vlookup value between the workbooks and worksheets.

The 6 Steps. Step 1: A place for your formula. Step 2: What are you looking for?

Step 3: Where are you looking? Step 4: What do you want to return? Step 5: Do you want to be precise or approximate? Step 6: Press enter.

Most people just put formulas into random cells. In this case 4 cells in total. Two for the name and two for the salary.

Select cells E2: Then click the horizontal line in the mid of the example. Now select a thinner border style and click the vertical line in the mid of the example.

Or watch the animation below. That was pretty simple, right?

But what it means is actually really simple. But enough with all the technicalities!

And there you have it! His salary!

Then you write it down or do whatever you want to do with it. So our lookup value is typed into cell F2 and then used in our function. Type this into Excel in cell F3 also shown in the screenshot right below: The last option is usually the best and our sheet is setup to have the name entered in cell F2.

Like done below: This step is where we are looking for our guy Nate. Now the reference to the range is locked and you can move on to the next step by typing a comma. Your formula should look like this: What you want to return. In short: Which column in the data you want to return data from.

That seems pretty straightforward, but you need to be aware of following: You selected the cells A2 through C That means that the data stretches 3 columns wide. Look at the picture below. Got it? Simply type 3 in your formula and move on to the next step by typing a comma. Your formula should now look like this. Excel deals with these 2 terms: Your formula should look like this by now.

In this example, the values are categorized like this: It should look like this: What do we usually do when completing a formula? We press enter.