When is vlookup used

The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. Skip to main content. Lookup a value in a table by matching on the first column. Return value. Excel Usage notes. Lookup with variable sheet name. In this example the goal is to create a lookup formula with a variable sheet name.

In other words, a formula that uses the sheet name in a way that can be changed by referring to up a value on the worksheet. The key to Get first text value in a list. Since we 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. One way to do this is to convert the values in the lookup column to numbers. An easy way to do this is to add zero using paste special. If you don't have easy control over the source table, you can also adjust the VLOOKUP formula to convert the lookup value to text by concatenating "" to the value like so:.

If you've ever built a series of nested IFs, you know that they work fine, but they require a bit of parentheses wrangling. You also have to be careful about the order you work in, so as not to introduce a logical error.

For example, a common use of nested IFs is to assign grades based on a score of some kind. In the example below, you can see a formula has been build with nested IFs to do just that, using the grade key at the right as the guide. This works fine, but note that both the logic and the actual scores are baked right into the formula.

If the scoring changes for any reason, you'll need to carefully update one formula then copy it down the entire table. A nice bonus of this approach is that both the logic and the scores are built right into the grade key table. If anything changes, you can simply update the table directly and the VLOOKUP formulas will update automatically - no editing required.

By design, VLOOKUP can only find values based on a single criteria, which is supplied as a lookup value to find in the first column of the table the lookup column. This means you can't easily do things like look up an employee with the last name of "Smith" in "Accounting", or look up an employee based on first and last names in separate columns. However, there are ways overcome this limitation. One workaround is to create a helper column that concatenates values from different columns to create lookup values that behave like multiple conditions.

For example, here we want find the department and group for an employee, but the first name and last name appear in separate columns. How can we lookup both at once? The background: imagine that you have a lot of order data, say, more than 10, records and you are using VLOOKUP to lookup the order total based on the order id. So, you are using something like this:.

You want an exact match because there's a chance that an order number won't be found. The problem is that exact matches are really slow, because Excel must proceed in a linear fashion through all values until it finds a match or not.

Conversely, approximate matches are lightning fast because Excel is able to do what's called a binary search. The second column is 2, and so on. In this example, the third parameter is 2. This means that the second column in the table is where we will find the value to return.

Since the table range is set to A1:B6, the return value will be in the second column somewhere in the range B1:B6. This parameter determines whether you are looking for an exact match or approximate match. A parameter of TRUE means that a "close" match will be returned.

To find an approximate match, use TRUE as the final parameter. Let's lookup a value that does not exist in our data to demonstrate the importance of this parameter! If no match is found, it returns the next smaller value which in this case is "Apples".

Let's modify our example above and assume that the table is in a different Sheet called Sheet2 in the range A1:B6. 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.