Introduction

The series so far:

• Excel, what’s occurrin’ – It doesn’t add up
• Excel, what’s occurrin’ 2 – Precision as displayed
• Excel, what’s occurrin’ 3 – Incomplete ranges
• Excel, what’s occurrin’ 4 – Number or text?
• Excel, what’s occurrin’ 5 – Getting iffy with it
• Excel, what’s occurrin’ 6 – Towards Zero
• Excel, what’s occurrin’ 7 – Conditional formatting from bottom to top
• Excel, what's occurrin' 8 - Data validation

Choosing items from a list

In part 8 of this series, we looked at Data Validation including the use of Data Validation to select items from a dropdown list. Sometimes, you might want to return the value of an item from one column in a list by choosing an item from a different column in the same row. Excel has several lookup and match functions that can help and, until recently, the most frequently used was probably VLOOKUP(). This function allows you to specify a lookup value which is then compared to the first column in a range that contains at least two columns. The third argument of VLOOKUP() specifies from which column number to return the value.

VLOOKUP() – warning: extreme danger

Here is our example, adapted from the previous article. We have changed our list of products to feature lower body wear and added a column of sales values. We use Data Validation in cell E2 with our Product column as our Source list. We have added our VLOOKUP() formula to cell F2 to return the value of sales for the product that we have chosen from our list:

=VLOOKUP(E2,ProductList,2)

Our VLOOKUP() function compares our Type entry in cell E2 with the first column of our ProductList Table and returns the value from column 2 of the first row that matches.

So far so good, but let’s now choose ‘Linen’ from our dropdown list, without making any other changes:

Oh no! It’s the wrong trousers!

Although we have selected ‘Linen’, our VLOOKUP() function returns the value from our ‘Cropped’ row. What could have gone wrong? In fact, nothing has gone wrong, VLOOKUP() is working exactly as intended.

The issue is the fourth argument of VLOOKUP() which we have not specified in our formula. This fourth argument defines the type of match that the function uses to find the right row. The choice is between an approximate match and an exact match. If the argument is set to TRUE or just omitted, VLOOKUP() defaults to performing an approximate match. You might think that ‘approximate’ just means the closest spelling to our match item to allow for a minor error when entering our match term – something that using a Data Validation list should avoid anyway. However, in the case of VLOOKUP(), approximate has a much more specific meaning.

VLOOKUP() performs an exact match by starting at the top of our list and checking each item against the match term until it finds a match. Before recent performance improvements, this meant that VLOOKUP() could take longer for longer lists. An approximate match is performed in a completely different way to speed up the operation. Instead of starting at the top and working through the list, the search for a match begins in the middle of the list and keeps halving the list of items to look through, thus massively speeding up the process compared with potentially checking every item. For this to work, the list must be sorted in ascending order, to ensure that the match searches in the right half of the list each time.

The more detailed explanation is that the approximate match finds the smallest item in a list, sorted in ascending order, that is smaller than, or equal to, the match value. To show how this works in our example, Cropped is the middle item in our list (which explains why our match for Cropped did return the correct value). For Linen, an approximate match assumes that the values in the list are sorted in ascending order so, given that Linen comes alphabetically after Cropped, it won’t try and match any values earlier in the list than Cropped. This process continues in our unsorted list until the match decides that the smallest item in our list that is greater than or equal to Linen is Cropped.

We can address the issue by always remembering to specify our fourth argument. If we set it to FALSE, our match will be exact, and the correct value will be returned:

We could also sort our list in ascending order but, if we were not using a Data Validation list, this would run the risk that making an error in entering our match value could result in an incorrect match rather than returning a warning error message.

XLOOKUP()

There is another way to sort out our approximate/exact issue. Excel now has an improved lookup function: XLOOKUP(). This defaults to performing an exact match, reducing the chances of a simple act of omission causing a catastrophic error with no warning. In addition, XLOOKUP() has a range of additional features that greatly improve its performance and flexibility compared to both VLOOKUP() and its horizontal expression: HLOOKUP():

Given XLOOKUP() is an all-round better option than VLOOKUP(), you might be wondering why we’ve bothered covering the legacy function. Whilst using XLOOKUP() in all versions of Excel that support it would be advisable, many people will come across spreadsheets that still use VLOOKUP(), either because of when they were originally created or because the creator was unaware of the introduction of the improved function. Such is the potential danger of the incorrect use of VLOOKUP() that knowing its deficiencies might prevent many a close shave.

Next time

Next time we will look at dealing with inappropriate dates when working with PivotTables.

Additional resources

You can explore all aspects of Excel, including many articles on lookup functions in general and XLOOKUP() in particular, in the ICAEW archive.