What are lookup functions?
Lookup functions in Excel are sometimes also called reference functions. The are used to find a specific piece of information from a table of data. You can think of them as digital reference books, like a dictionary or an encyclopedia.
As an example, suppose we have a table of employee information. It could include things like an employee ID, name, department, hire date and salary. If we know an employee’s ID number, we might want to find the employee’s name. Or, we might know an employee’s name and need their ID number.
There are a number of functions available, and each has a specific purpose. You can think of them as tools in your toolbox. Choosing the right tool depends upon what you are trying to do. We can fasten objects together using either a screwdriver or a hammer. However, they are not substitutes for one another.
What are you trying to do?
How is your data organized?
If your data is organized by-row, to find a piece of information you would look row-by-row to find it. For example, to find the last name for employee number 3, we would look down the employee ID column until we find number 3, then would look across that row to find the name, department and other related fields.
Which functions to choose?
- If you (and anyone else who might use your spreadsheet) are using the latest version of Excel (Microsoft 365 subscription) I would always recommend that you use XLookup.
- If your spreadsheet needs to be compatible with an older version of Excel, then your choice depends on how you will be accessing the data.
- If the information you are looking for is always in a column to the right of the lookup column, then you can use VLookup.
- For example, if you are trying to find the department for an employee with last name “Wont”, then you can use VLookup.
- If, on the other hand, you want to find the employee ID of an employee with last name “Wont” then VLookup will not work.
- If Vlookup is not appropriate, then you should use Index and Match. As we will see later, Index and Match can be combined to find information within a data table regardless of how it is organized.
- If the information you are looking for is always in a column to the right of the lookup column, then you can use VLookup.
If your data is organized by-column, to find a piece of information you would look column-by-column to find it. To find information about Toyota, for example, we would look in column “E” in our example.
Which functions to choose?
- If you (and anyone else who might use your spreadsheet) are using the latest version of Excel (Microsoft 365 subscription) I would always recommend that you use XLookup.
- If your spreadsheet needs to be compatible with an older version of Excel, then your choice depends on how you will be accessing the data.
- If the information you are looking for is always in a row below the lookup row, then you can use HLookup.
- For example, if you want to find when Toyota was founded, you could use HLookup.
- If, on the other hand, you want to find the name of a company headquartered in Japan, HLookup will not work.
- If Hlookup is not appropriate, then you should use Index and Match. As we will see later, Index and Match can be combined to find information within a data table regardless of how it is organized.
- If the information you are looking for is always in a row below the lookup row, then you can use HLookup.
Summary
- If you have a Microsoft 365 subscription and are using the latest version of Excel, I would recommend that you use XLookup. It is faster than any of the others, and is the most flexible. A potential downside is that it is not compatible with older versions of Excel.
- The next most-general choice is to use Index, possibly in combination with Match. This choice will work if you need to refer “backwards” in your database.
- Your next choice would be VLookup for by-row data tables, and HLookup for by-column data tables.
- Lookup exists to be compatible with some other spreadsheet programs. Iit has limited capability, however, and I would not use it.
- Similarly, Choose is probably not one you will ever use, but to be complete I’ve included it here.
- Sumifs is not technically a lookup function. However, I find that it can be useful to extract numerical data from a data table. It is especially useful if you need to lookup with multiple terms (keys).