VLOOKUP… to the Left!

Have you ever been attempted to use VLOOKUP in a large data set, only to realize the data you’re seeking is to the left of the lookup column? Sure, you could cut/paste the lookup column the left of the table, but that might interfere with other parts of the dataset.

Overview

The VLOOKUP formula is one of the most popular non-arithmetic formulas in Excel. It’s often the first formula a user learns to present dynamic content — opening up new functionality with table lookups and data validation.

Despite its popularity and broad usage, forums all over the internet are littered with questions about how to use VLOOKUP to reference seek to the left of the lookup column. You can find effective and practical VLOOKUP to-the-left solutions here. For those of you looking for a brute force VBA solution, read on.

Example File

The following macro-enabled Excel file contains all example scripts presented in this post. Macros must be enabled upon opening.

VLOOKUP to the Left

Enter VLOOKUPLEFT, a user-defined VBA function that also works as an Excel formula. In addition to containing all the features and functionality of the normal VLOOKUP formula, using this function as a formula will permit items to be referenced to the left of the search column.

How it Works

The normal VLOOKUP function requires the following parameters:
[glossary_exclude]

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

[/glossary_exclude]
The VLOOKUPLEFT function requires the same parameters:
[glossary_exclude]

=VLOOKUPLEFT(lookup_value, table_array, col_index_num, [range_lookup])

[/glossary_exclude]
The only difference in implementing the VLOOKUPLEFT formula relates to the col_index_num argument. Unlike the normal VLOOKUP formula, the VLOOKUPLEFT formula can accept negative values as an argument for col_index_num. A negative col_index_num value will tell the VLOOKUPLEFT function to count that number of columns from the right of the table, moving left from the lookup value to the seek value.

The VLOOKUPLEFT formula behaving like a normal VLOOKUP:

=VLOOKUPLEFT(“Connecticut”,A2:E51,3,FALSE)

Implementation of VLOOKUPLEFT actining like a normal VLOOKUP, looking from left to right.

Nothing fancy here. I included normal VLOOKUP functionality for seamless integration. Rationale being that users will not need to switch between formulas.

The VLOOKUPLEFT formula looking from right to left:

=VLOOKUPLEFT(“Connecticut”,A2:E51,-3,FALSE)

Implementation of VLOOKUPLEFT formula looking from right to left.

If you look very closely at the two formulas above, the only minor difference is a negative sign in the 3rd argument (bold and red text in both examples). As previously mentioned, this negative value tells the function it must now look from right to left to find the seek value.

VLOOKUPLEFT also has an approximate match mode, but like the normal VLOOKUP function, the search value range must be in alphabetical (or numeric) order for approximate match to work properly.

Installing in Your Workbook

There are two methods of installing this function for your own use, each outlined in the next two sections. Regardless of method, you will need to enable macros to use the function.

A special note about VBA user-defined functions in the 32-bit Excel: after being prompted to enable macros in the workbook, the formulas will need to be re-evaluated. This can be done through a full worksheet calculation, by pressing Ctrl-Alt-F9 simultaneously. The function will behave normally once this initial recalculation is done. This step should not be required in the 64-bit version of Excel.

Use within example file:

You can add and remove sheets from the example file as needed. Simply copy/paste existing data into a new sheet and delete the example sheets.

Install function into an existing Workbook:

This option is for slightly more experienced Excel users. The function script can be copied and pasted into a VBA coding module. The full function script is included in the next section — it can be copied by pressing the 2nd icon from the right in code sample’s toolbar.

If you need additional help, you can read about accessing the VBA editor, and creating a new module.

Function Script

I’m going to highlight a few coding approaches in the sections below. Before getting to those details, the following is a list of links to core VBA concepts included in this procedure:

  • The Range Object — For referencing range parameters and pulling values.
  • Multi-Dimensional Arrays — The table range is converted to a multi-dimensional array to find the height and width of the range.
  • For Loops — Loos through the rows of the range to search for the lookup value.
  • Conditional Statements — Tests various criteria to see if a value is an approximate or exact match.
  • Logical Operators — Used to consolidate complex conditions for approximate or exact match.
  • String and Numeric Functions — Included in conditionals to test various criteria for the lookup value.

Approach

This function focuses around a For Loop which iterates through each row in the selected range paramTableRange. Most of the complexity within the loop relates to the “Approximate Match” option (lines 32 to 50), which will return the closest available match if the optional paramRangeLookup parameter is left blank (default True) or explicitly set to True. Separate tests must be executed depending on if the lookup value is a number or string. Numbers are tested using simple less than/greater than operators, while strings are tested using the intrinsic StrComp function. A third test will determine if the bottom of the range is reached, returning the last value if none of the other tests succeed.

Other important functionality relates to identifying the columns and rows that the function should look through. While it’s easy to extract the coordinates of the top row and left-most column of a multi-cell range, extracting the coordinates of the bottom row and right-most column is not as easy. To accomplish this, the function converts the range into a multi-dimensional array and uses the UBound function to determine the array’s size. The array size values are then added to the range’s top-left anchor coordinates to get proper dimensions.

Potential Improvements

There are a few things that could run more efficiently in this procedure.

First, the procedure relies heavily on the variant data type. This may cause some speed and overhead issues, especially if this function were used as part of a large dataset. Given that users can enter a Range, string value, or numeric value for the paramLookupValue, it’s hard to avoid this. However, I could potentially run a conditional test to determine data type, then convert the variable to that type. This would lead to some efficiency when the data is used in the For Loop.

Second, given that the range has been converted into a multi-dimensional array, I could have potentially used that to search for the value, as opposed to referencing the spreadsheet. I ran into some issues with this approach when using the approximate match conditionals. I may revisit at some point and post an update.

Finally, the approximate match mode in VLOOKUPLEFT does not operate identically to the normal VLOOKUP formula. Both formulas require the search range to be in alphabetical or numeric order, and both seemed to work well with numbers and minor miss-spellings. Both have trouble with any significant misspellings, however, the VLOOKUPLEFT returns different “incorrect” results.

Feedback

  • Update December 17, 2017: Special thanks to RubberDuck VBA. Reader made the suggestion of changing the assignment value on line 63 from string “#N/A” to the constant returned from CVErr(xlErrNA). This permits proper application-side error handling with IFERROR, ISNA, etc.

Any other thoughts on this approach? Would you have done something differently? Let me know in the comments.

 

2 Comments

Leave a Reply