In the above table, the employee IDs are in column 1 on the left and the email addresses are in column 4 to the right. When lookup values appear in the first leftmost column, this limitation doesn't mean much, since all other columns are already to the right. However, if the lookup column appears inside the table somewhere, you'll only be able to lookup values from columns to the right of that column.
This article explains the topic in detail. This makes sense when you want to lookup information based on a unique key of some kind, for example, product information based on a product code, or movie data based on a movie title:. However, you'll want to use approximate mode in cases where you're not matching on a unique id, but rather you're looking up the "best match" or the "best category".
For example, perhaps you're looking up postage based on weight, looking up tax rate based on income, or looking up a commission rate based on a monthly sales number. In these cases, you likely won't find the exact lookup value in the table. This name is not intuitive, so you'll just have to memorize how it works. This is a dangerous default because many people unwittingly leave VLOOKUP in it's default mode, which can cause an incorrect result when the table is not sorted. To avoid this problem, make sure to use FALSE or zero as the 4th argument when you want an exact match.
These two formulas are equivalent:. This a clear indication that the value isn't found in the table. These 3 formulas are equivalent:. That way, you always have a visual reminder of the match mode you expect. If you are using approximate-mode matching, your data must be sorted in ascending order by lookup value.
Otherwise, you may get an incorrect results. Also be aware that sometimes text data may look sorted, even though it's not.
Felienne Hermans has a great example of this problem here , from a cool analysis she did on actual Enron spreadsheets! For example, perhaps you have order data in one table, and customer data in another and you want to bring some customer data into the order table for analysis:. One to pull in the customer name, and the other to pull in the customer state. If you ever need to apply arbitrary categories to data records, you can easily do so with VLOOKUP, by using a table that acts as the "key" to assign categories.
In the example below, we are using VLOOKUP to to calculate a group for each department using a small table named "key" that defines the grouping. For more information, please see How to Vlookup from another sheet in Excel. In theory, you can use the above approach to Vlookup more than two criteria. However, there are a couple of caveats. Firstly, a lookup value is limited to characters, and secondly, the worksheet's design may not allow adding a helper column.
Luckily, Microsoft Excel often provides more than one way to do the same thing. For example, to look up based on 3 different values Date , Customer name and Product , use one of the following formulas:.
But what if there are several matches in your lookup array and you want to get the 2 nd or 3 rd instance? The task sounds quite intricate, but the solution does exist! Suppose you have customer names in one column, the products they purchased in another, and you are looking to find the 2 nd or 3 rd product bought by a given customer.
The simplest way is to add a helper column to the left of the table like we did in the first example. But this time, we will populate it with customer names and occurrence numbers like " John Doe1 ", " John Doe2 ", etc. After that, input the target name and occurrence number in separate cells F1 and F2 , and use the below formula to Vlookup a specific occurrence:.
If you are looking for the 2nd instance of the lookup value, then you can do without the helper column. Please note that the above formula is written for a specific case where data cells in the lookup table begin in row 2.
Once your formulas are in place and saved, Excel will reference its location. If you move the workbook and save it in another location, you will need to relink it to your formula. The brackets surround the workbook name, the single quotes surround the name of the worksheet, and the exclamation point signals to Excel that it must draw the table from another workbook and worksheet.
For more information on referencing data from another workbook, see here. You may need to review lists of data to see what information exist in both lists or what is missing in one of your lists. For this task, your end-result can be either the unique values, the duplicated values, or a proxy value such as 0 when the data is in only one list and 1 when the data appears in both lists.
We have a List of Participants column and a separate list of employees in the department on the worksheet. As noted above, you could also perform the search by referring to a list in another worksheet or workbook; just follow the syntax for these functions above.
The first extra column is for the values duplicated in the two columns, and the second is for identifying the unique values in column C those who participated, but are not members of the sales department. C:C refers to column C List of Participants , which you are checking your reference name against.
This is the column reference number. Since your data table has only one column, the column number is 1. As you can see, the staff that participated in field day showed up by name in column E.
In the above example, you compared two lists to find an overlap. As an Excel expert, you know this means that your formula was successful. You could also easily return a zero 0 or another string such as the phrase Not present.
The new formula is:. This formula will be:. Excel performs functions from inside out. For the formula in cell G2, Excel performed the functions in the following order:. Each function looks at the return from and bases its formula on the prior function.
After you enter the new formula, click Enter on your keyboard and drag the formula down through cell G23 to cover your whole list of participants. You can even include a message or a zero 0 in the quotes. This combination is an effective and quick way to compare data in different columns and present it as you need. One final type of error is the NUM! This type of symbol is viewed in the formatting of a cell but should not be manually entered.
To remove such errors, review the characters and formats of your cells. However, the real world often requires you to use two or more criteria when looking up data from a database. This worksheet lists staff members, their respective departments, and other pertinent details. To get the right John Smith for your lookup, you will need to make each John Smith unique in the first column of your table.
Keeping in mind the best practices of performing a lookup in Excel, insert a lookup box into your spreadsheet that is at least one column or row away from your database for your VLOOKUP formula. Select column B in your worksheet, insert a column to the left, and label it Name-Department. Click Enter on your keyboard. Concatenation is a great way to account for multiple-string search criteria.
Since there may be multiple people with the same name, use both criteria to identify the correct person. Insert lookup boxes in the same manner as you did in the exercise for a VLOOKUP with multiple criteria, spacing them at least one column or row away from the database. This possible drain on your computing power is due to the nature of Excel having to check every cell more than once for the required criteria. Adding an array to your normal formulas essentially directs Excel to create and perform functions on virtual data.
This is data you will not see in any cell but is still calculated. Manually typing in these curly brackets on the outside of the formula will not create an array formula. This worksheet has data about a sales staff and how much each employee sold in the first quarter. You could easily add another column to this worksheet to sum up the columns, but if you wanted to do this calculation on another worksheet or workbook with thousands of lines of data, it gets complicated.
A combined formula is the most prudent route. Since this covers multiple criteria, you will add an array. The columns Excel moves over to report back. The formula is then wrapped in a SUM function, since the return is multiple values in an array. The formula is wrapped in a MAX function, since it returns multiple values in an array.
One neat trick when working with arrays: Click the F9 key while selecting a portion of the formula. Clicking the Esc key sets the formula back to normal. This worksheet tab has sales staff and their sales per month for the first quarter. You are tasked with figuring out their bonuses, which are calculated monthly and based on a monthly threshold in the Percent Bonus Table. You can use a named range or a table, and you can use names in the argument instead of cell references.
The cell range also needs to include the return value you want to find. Learn how to select ranges in a worksheet. This is the default method if you don't specify one. The range where the lookup value is located. For example, if your lookup value is in cell C2 then your range should start with C. The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.
If you don't specify anything, the default value will always be TRUE or approximate match. This can be especially useful if you need to share a workbook with people who have older versions of Excel that don't support data features with multiple tables as data sources - by combining the sources into one table and changing the data feature's data source to the new table, the data feature can be used in older Excel versions provided the data feature itself is supported by the older version.
The field that links the tables listed in parentheses in the dialog box. The Related Lookup Table name. The field column in the Related Lookup Table that has the data you want in your new column.
0コメント