In a couple of previous posts I threw in some of Excel lookup examples and I have received a few comments from people saying that they have trouble with them and asking if there is a simple way to remember how to use them.
The two main functions VLOOKUP and HLOOKUP are pretty easy to understand but there are a couple of basic limitations which I suspect either catch people out or make them think that they aren’t worth using.
I’ll run through the basics and then look at the future of lookups which can help address the issues.
VLOOKUP
VLOOKUP is a function for conducting vertical lookups – that is to say that it you are looking up a value in the first column of a table of data and returning a value from another column of that same table.
So here for example we would be trying to match a value in the ID column and returning one of the other values – for example Unit.
The syntax for the command is as follows:
VLOOKUP(search_term, table_range, offset, [range_lookup])
So the parameters are in basic English:
- search_term = what you are looking for in the first column
- table_range = where is the data that you are looking for, this can be either a range for example A4:E20 or of course a table as per our previous posts
- offset = which column of the matching row should be returned – starting with 1 being the search column so in our table above, if you wanted the Item name that is column 2, unit is 3 and so on
- range_lookup = either TRUE which returns approximate matches or FALSE which needs an exact match. If you miss this element off it defaults to TRUE which is the first gotcha to watch for – FALSE almost always gives better results so in my experience I nearly always specify to avoid odd results
So, having created a table (tblVlookupExample) to return values we would use the following:
=VLOOKUP(H4,tblVlookupExample,2,FALSE)
This returns the Unit associated with the ID, in this case Widgets
The stock value column above is created by looking up two columns and multiplying them as follows:
=VLOOKUP(H4,tblVlookupExample,3,FALSE)*VLOOKUP(H4,tblVlookupExample,4,FALSE)
HLOOKUP
HLOOKUP works in a very similar way but uses a match in the top column of a dataset to access data in the rows below.
The syntax for the command is as follows:
=HLOOKUP(search_term, table_range, offset, range_lookup)
The parameters should look familiar to the VLOOKUP ones and as as follows:
- search_term = what you are looking for in the first row
- table_range = where is the data that you are looking for
- offset = which row of the matching column should be returned – starting with 1 being the search row so in our table above, if you wanted the Unit cost that is row 2, stock is 3 and so on
- range_lookup = either TRUE which returns approximate matches or FALSE which needs an exact match. If you miss this element off it defaults to TRUE which is the first gotcha to watch for – FALSE almost always gives better results so in my experience I nearly always specify to avoid odd results
So, in our example data above if we wanted to know the value of Do Dats we would use:
=HLOOKUP(I12,A12:F15,4,FALSE)
Limitations
With both of the traditional lookups there are limitations. The first as mentioned is that their defaults for the optional element is probably not the one you want to use.
More serious is the fact that the lookup has to be against the first row or column respectively which may limit the usefulness of it as a tool. If you take the VLOOKUP example above, how many people will be able to remember what product ID reflects the item they want data on? OK so with 5 example it’s not a problem but 500!
If we are in total control of the data we could structure it in such a way that the thing we are most likely to want to search on is in the first row/column but that still limits us to searching just that. The reality though is that the data in the first example is probably how a lot of data will be created, a unique ID followed by the other information. We may not even have control of that data source, it may be being imported from somewhere else.
There are other issues such as only being able to return one element at a time or the fact that if you are not using a validated list (see the earlier post for that) and whoever is using doing the lookup enters a value that isn’t in the table (either because it isn’t there or they have mistyped) Excel will return a #N/A error which isn’t especially helpful.
There are some ways to address these issues, the latter by using some of Excel’s built in error handling functions and we can even overcome the first row issue by a mixture of the INDEX() and MATCH() functions but this does start to complicate matters.
However, if you are using the Office 365 version of Excel or later there is a new kid in town.
Enter XLOOKUP
At first, XLOOKUP can seem to be more complex than the older siblings. The syntax is as follows:
=XLOOKUP(search_term, lookup_range, return_range, [not_found], [match_mode], [search_mode])
However, the first thing to note is that the optional terms (those in [ ]) default to what is probably the most sensible option for most uses. So unlike the earlier versions it will default to exact match for example.
The terms in simple English are as follows:
- search_term = what you are looking for
- lookup_range = where are you looking for the search_term
- return_range = what part of the data do you want to return if we find the search_term
- not_found = optional message should we not find what we are looking for
- match_mode = 0 by default for exact match, -1 is exact match or next smallest, 1 is exact match or next biggest and 2 for a wildcard match
- search_mode = 1 by default for search from first item, -1 for last, 2 for binary ascending and -2 for binary descending
This may all still look confusing but to show that this is in fact simplicity itself lets recreate the examples above using XLOOKUP.
For our VLOOKUP we had to search on the ID which wasn’t practical.
If we want to do a simple lookup using the more workable Item column of data we could use something like:
=XLOOKUP(S3,tblVlookupExample[Item],tblVlookupExample[On Order])
Here we are not limited to the first column but instead search in the Item column and then return the appropriate row from the On Order column.
Now to return the stock value of our Items as we did in our earlier example we simply search for the item and then return the values we want as follows:
=XLOOKUP(P3,tblVlookupExample[Item],tblVlookupExample[Unit])*XLOOKUP(P3,tblVlookupExample[Item],tblVlookupExample[Stock])
For our HLOOKUP example we instead use:
=XLOOKUP(L12,B12:F12,B15:F15)
As you can see this exactly matches the earlier result.
Getting A Bit More Complicated
The XLOOKUP command can be extremely powerful and I’m not going to attempt to cover all the possible options here but there are a couple of areas that it is worth covering.
The first is the error message if not found. For this example I am using the following data (tblCraft) and then a simple lookup based on material type:
The lookup is:
=XLOOKUP(C4,tblCraft[Material],tblCraft[1st])
As the material hasn’t been entered we see the standard error message of #N/A
Now if we instead use the following:
=XLOOKUP(C4,tblCraft[Material],tblCraft[1st],"Sorry, material not available")
We get:
And of course if we do select a valid material we get the desired result. If we type an incorrect material we get our error back.
The second useful feature is that XLOOKUP can return an array rather than a single cell so for this example we are going to use a simple employee list (tblEmployee) and then create a form that will take an employer ID and return all of the data on that employee:
As you will see, we have used a custom error for when we don’t have a valid ID. Now, in cell H5 (EmpID) we want to enter our ID and then fill the rest from the data so in cell I5 (Forename) we type the following:
=XLOOKUP(H5,tblEmployee[EmpID],tblEmployee[[Forename]:[Base]],"Please enter valid employee")
This tells it to look for the ID in the ID column and then return the relevant row, in this case using the table data from the top of forename column to the end of the base column (i.e. everything except the ID column and headers). This then populates the rest of the table as:
As you can see, because it returns an array the formula in I5 automatically fills across to the other columns with the data contained in the data table. Changing the value in the EmpID column updates the remaining columns.
Finally, a quick look at how we might use match_mode when we want to not require an exact result. For this example we are going to use a simple table that has a discount based on how many of a particular item someone buys.
Now we will use our data to return a discount based on the number we want as normal.
=XLOOKUP(D12,tblDiscount[Qty],tblDiscount[Discount])
As you can see, if we match the exact discount we are OK, however if we change to a number that is not in the list we get errors:
So instead we can enter the command with an approximate match using -1 in the command:
=XLOOKUP(D12,tblDiscount[Qty],tblDiscount[Discount],,-1)
As you can see this now returns the result we expect, as we are above the 10 threshold but below the 100 we get 5% discount applied. If we go higher it will pick the nearest appropriate discount:
If instead of -1 we used 1 it would select the next largest although if we applied that to discounts we may not stay in business very long!
Hopefully that provides a useful introduction to lookups. If there are any other areas of Excel that you would like to see please drop me a note in the comments and I’ll add it to the list of future posts.
