Building on VLOOKUP
Before we discover new LOOKUP functions in Office 2021, we will build on our existing VLOOKUP skills cultured from our previous edition book, Learn Microsoft Office 2019. It is important to understand how different functions work together or to find fewer complex methods to generate the desired output.
Combining IFERROR AND VLOOKUP
As mentioned, we will look at the new XLOOKUP function in subsequent topics, but we will fi stly look at how the IFERROR and VLOOKUP functions can complement each other:
- Open the workbook named VLOOKUP.xlsx.
- Click to select the IFERROR worksheet.
- Cells A2:G9 house employee data for BizGen Ltd. Column F is missing team data. The team data is kept in a separate worksheet named Team. As we do not want to re-type this data, we will construct a VLOOKUP formula to look up the name of the colleague, and pull through the corresponding Team name located in the TEAM worksheet.
- We have learned all about the VLOOKUP syntax in the previous edition of our book, so let’s click into cell F4 to construct the formula, as follows:
Figure 10.14 – The VLOOKUP syntax showing the different arguments applicable to the example
- Press Enter to see the result of the first colleague and then fill in the rest of the cells down the column.
Figure 10.15 – The VLOOKUP result showing #N/A where errors are located
- The result indicates an #N/A error for the colleague named Suzette Mann. In this case, we would need to compare the result with the Team worksheet. We have to be mindful that VLOOKUP could report an incorrect result, as well as the dataset you are using as table_array. In this case, there is a discrepancy between the spelling of the colleague in the source dataset and table_array on the Team worksheet. We could fix the spelling error, and the formula would work perfectly by duplicating the Team name. A few other reasons for the #N/A error could be the following:
- The lookup value is not located in the lookup array.
- The data may not be cleaned, especially if imported. See the relevant topic in the previous chapter to find out more about how to clean data ready for input.
- There could be extra spaces or spelling errors causing the return of the #N/A.
- We can add the IFERROR function to the VLOOKUP function so that it reports a specific text as the error in return, instead of the default #N/A error. To identify duplicates at a glance on the worksheet, you could use the text “duplicate” or “error found”, for example.
- The IFERROR function syntax is as follows: IFERROR(value,value_if_error).
- Double-click on cell F4 to amend the formula. As the existing function already contains the value argument, we can add the IFERROR function at the start of the formula and then include the value_if_error part of the syntax at the end.
- The formula should read as follows:
=IFERROR(VLOOKUP(C4,TEAM!A1:B8,2,FALSE),”ERROR FOUND”).
Figure 10.16 – The IFERROR function added to the existing VLOOKUP formula
- Now we can clearly see that there is an error in cells F7 and F8. A great way to ensure that errors are visually highlighted on the worksheet is to apply conditional formatting.
Let’s visit an example that uses the approximate match as the range lookup.
Looking up using an approximate match
Th s is a useful example for human resource departments wanting to pull through the salary band for employees by looking up a salary range or scale using the approximate match:
- Continue to use the same workbook as the prior example, but click to access the Band worksheet. This sheet contains a list of employees and their salaries, as well as a salary band data range. We would like to automatically populate the salary band for each employee in the list.
- As we are already familiar with the function syntax, we can click into cell C4 to construct the formula as follows: =VLOOKUP(B4,$E$4:$G$11,3,TRUE).
-
Ensure that table_array, E4:G11 is marked as constant and that [range_lookup]
is set to TRUE. TRUE indicates an approximate match here as we are dealing with looking up the colleague’s salary from a salary range and returning the appropriate salary band.
Figure 10.17 – VLOOKUP function constructed using the approximate match
Lastly, let’s combine VLOOKUP with a validation rule and the MATCH function.
