Can you vlookup based on two criteria

After installing Kutools for Excel , please do as this:. In the Multi-condition Lookup dialog box, please do the following operations:.

Then, click OK or Apply button, all the matched values based on the multiple criteria have been extracted at once, see screenshot:. Note: The other languages of the website are Google-translated. Back to English. Log in. Remember Me Forgot your password? Password Reset.

Please enter the email address for your account. A verification code will be sent to you. To input formulas in Excel, you can type them directly into the cells or use the function wizard. It is difficult, however, to write formulas in the wizard and check them as you go pressing F9. One tip with Excel is to write from the inside out. Since Excel computes the innermost function s first, you should understand what each returns before you wrap it into another function.

Another tip when preparing your data to write formulas is to name your table ranges. For example, cells A1:B18 refer to the following table:. An even better way to maintain the reference for this table is to transform it into an Excel table by selecting Creating a Table on the Insert tab and renaming it in a manner consistent with its purpose.

For more information on creating tables in Excel, see this article. To accomplish this task, click the cell and a green box will appear.

You can save time and energy by writing formulas that can be used to populate other cells. Watch the demo to see how you can more effectively manage your team, projects, and processes with real-time work management in Smartsheet. Watch a free demo. Pulling data from one Excel worksheet to another is a matter of writing the address of the data table into the formula correctly.

For more information on how to reference data in another workbook, see here. Similar to pulling data from one worksheet to another, you can also pull data from one workbook to another. In this case, make sure that the workbook with the data you are referencing is open when you write your formula. 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. It ignores to all empty sets of data elements. And non-empty elements are mapped to the value of the cell C1, that used as the first criterion of the search query Date.

With a positive result of the mapping, this function returns the value of the element from the third column revenue of the conditional table. This is The lookup table is the named range "key" B5:C9. Finally, the The number 3 indicates the 3rd column in the table last name and FALSE is supplied to force an exact match Related functions. Related videos. This is good for tax rates, postage, commissions, and the like.