Vlookup what does it do

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 Map inputs to arbitrary values. This formula uses the value in cell F6 for a lookup value, the range B5:C10 for the lookup table, 2 to indicate "2nd column", and zero as the last argument to force an exact match.

Although in this case we are mapping In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value. There are Related videos. How to select arguments with the formula tip window. In this video we'll look at the best way to select function arguments in a formula: the function screen tip window. You can even use this technique to reorder the columns you retrieve.

Related functions. See also. Email HP. Vlookup short for 'vertical' lookup is a built-in Excel function that is designed to work with data that is organised into columns. For a specified value, the function finds or 'looks up' the value in one column of data, and returns the corresponding value from another column.

Imagine that your company keeps a spreadsheet of employee hourly rates of pay see 'Hourly Pay' spreadsheet below.

At the end of each week, the sales team manager sends you a list of hours worked by each of his staff during the week see 'Sales Team Hours' spreadsheet below.

For each listed relationship, note the following: The field that links the tables listed in parentheses in the dialog box. Don't store number or date values as text. Make sure your data doesn't contain erroneous characters. Need more help? Expand your skills. Get new features first.

Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. By preceding the table range with the sheet name and an exclamation mark, we can update our VLOOKUP to reference a table on another sheet.

Let's throw in one more complication. What happens if your sheet name contains spaces? If there are spaces in the sheet name, you will need to change the formula further. Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:B6, now we need to wrap the Sheet name in single quotes as follows:. For example, if you wanted to have the table portion of the VLOOKUP formula be from an external workbook, we could try the following formula:.

Now it is important for us to cover one more mistake that is commonly made. When people use the VLOOKUP function, they commonly use relative referencing for the table range like we did in some of our examples above. This will return the right answer, but what happens when you copy the formula to another cell?

The table range will be adjusted by Excel and change relative to where you paste the new formula. Let's explain further