Your Questions Answered #2 - Are there any advantages of still using VLOOKUP over XLOOKUP (or INDEX and MATCH)?

In a recent webinar on ‘Excel Error Horrors (and how to avoid them)’, Liam Bastick explored how to spot errors that are regularly made when using common functionality in Excel including VLOOKUP, dynamic arrays and linking files. Liam returns to answer questions from the webinar he was unable to respond to during the session.

Are there any advantages of still using VLOOKUP over XLOOKUP (or INDEX and MATCH)?

Oh goodness, you are asking the wrong person! I would rather cut off my right thumb than use VLOOKUP to be honest… However, fellow Excel MVP Charles Williams amongst others has tested its speed and concluded it is a much faster function than many of its peers when it works. I prefer accuracy over speed though.

There are numerous ways to break it such as no built-in error handling for the exact match scenario and duplicates can still lead to erratic behaviours when used incorrectly. To be fair, XLOOKUP does not work in all versions of Excel presently and cannot be used directly when the lookup range and the reference range have transposed dimensions (e.g. a row containing seven columns and a column containing seven rows). For me, INDEX and MATCH is the best “all-rounder” although XLOOKUP is more powerful where recognised. I also admit I tend to use LOOKUP when working with dates as it only requires one function (as mentioned below, VLOOKUP requires COLUMNS).

Can you please explain the use of the COLUMNS function in the VLOOKUP calculation?

VLOOKUP always looks for the lookup_value in the first column of a table (the table_array) and then returns a corresponding value so many columns to the right, determined by the col_index_num column index number.

In this above example, the formula in cell G25 seeks the value 2 in the first column of the table F13:M18 and returns the corresponding value from the eighth column of the table (returning 47).

Pretty easy to understand – so far so good. So, what goes wrong? Well, what happens if you add or remove a column from the table range? Adding gives us the wrong value:

With a column inserted, the formula contains hard code (8) and therefore, the eighth column (M) is still referenced, giving rise to the wrong value. Deleting a column instead is even worse:

Now there are only seven columns, so the formula returns #REF!

It is possible to make the column index number dynamic using the COLUMNS function:

COLUMNS(reference) counts the number of columns in the reference. Using the range F13:M13, this formula will now keep track of how many columns there are between the lookup column (F) and the result column (M). This will prevent the problems illustrated above.

This should also include the fourth argument range_lookup as well to avoid errors:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The robust formula needs both COLUMNS and a fourth argument of FALSE to work as expected:

You advocate consistency in calculations. Shouldn’t you use a consistent sign convention so far as possible, so that all calculations are additions on output sheets?

There are definitely different arguments out there regarding consistency versus transparency etc. I prefer all numbers are positive when they match the description, regardless of whether they are inflows, outflows, income or expenditure (unless the text starts with “less:”). This leads to less confusion when some items are added and others deducted (e.g. adding back disallowable expenses but deducting non-assessable revenues), plus means reviewing costs is not as perplexing. For example, how would you evaluate the following: “Costs were $(10,000) last year but have increased (10)% this year…”?

How do you unblock macros in Excel?

Microsoft has recently upped its game with blocking macros. They argue that macro-based malware is on the rise and thus they are blocking macros from more and more external / third party locations. Protected mode seems insufficient as we have noted the following message becoming more and more prevalent when opening files (we have opened time and time again previously!):

Of course, everything to avoid digital nasties is to be applauded, but innocent files are being ensnared too. Trying to enable macros in the usual way through the Trust Center (sic) will not work here. In this instance, close the file and locate it in File Explorer. Then, right-click on the file:

Click on ‘Properties’ (ALT + ENTER), which will produce the following dialog for the selected file:

At this stage, click on Unblock in the bottom right-hand corner of the General tab. The file may now be opened with the option to enable macros restored. Of course, use this trick wisely – only tick ‘unblock’ if you are confident that the file has come from a legitimate source!

And just to answer one other question: if you don’t see the option to unblock, the file isn’t blocked!

How do I get the example files?

You can download the example files and access a recording of the webinar though this link:

• Excel error horrors (and how to avoid them)