When do i use vlookup in excel

Enter your table array or lookup table, the range of data you want to search, and a comma: H2,B3:F25,. Enter column index number. This is the column where you think the answers are, and it must be to the right of your lookup values: H2,B3:F25,3,. When you need to find information in a large spreadsheet, or you are always looking for the same kind of information, use the VLOOKUP function.

VLOOKUP works a lot like a phone book, where you start with the piece of data you know, like someone's name, in order to find out what you don't know, like their phone number. So, as an example, I'll enter part numbers, the thing I know, and find out Prices, the thing I don't know. These parentheses will contain a set of arguments, and an argument is just a piece of data that the function needs in order to run.

Follow that with a comma, and then, I'll enter the range of cells that contains the data I want to search. That's this block of data here. The part numbers start in cell B3, and if I scroll down, you can see the status values end at cell E Next, I'll type the number 3.

So what just happened? Now look through this range of cells, and in the third column to the right, find the value on the same row. Table of contents. Next: Advanced IF functions. Excel training. Usage notes. Due date by category. In this example, the goal is to create a due date based on category, where each category has a different number of days allocated to complete a given task, issue, project, etc.

The amount of time available to resolve This formula is based on a simple grading example The goal in this example is to create a self-contained lookup formula to assign a grade to the score in cell E7, based on the table in B6:C Make words plural.

In this example, the goal is to make a noun plural when the number of items is greater than one. In many cases, a noun can be made plural by adding an "s". However, many nouns have an irregular plural form, and the main The ID value comes from cell I4, and is locked so that it won't change as the formula is copied down the Note: the last In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value.

There are Find missing values. You can use behavior directly inside an IF statement to mark values that We'll add our formula to cell F2 , but you can add it to any blank cell. Then type the formula name. Our arguments will need to be in parentheses, so type an open parenthesis. So far, it should look like this:.

Now, we'll add our arguments. The first argument is the name of the item you're searching for, which in this case is Photo frame. Because the argument is text, we'll need to put it in double quotes:.

The second argument is the cell range that contains the data. In this example, our data is in A2:B As with any function, you'll need to use a comma to separate each argument:. In this example, it will search column A for "Photo frame". The value that it returns in this case, the price will always need to be to the right of that column. The third argument is the column index number.

It's simpler than it sounds: The first column in the range is 1, the second column is 2, etc. In this case, we are trying to find the price of the item , and the prices are contained in the second column.