Bottom line: Get a preview of a new app I've been working on that helps with writing lookup formulas like VLOOKUP or INDEX & MATCH.
Skill level: Intermediate
Download the Example File
You can download the sample file that contains the code below.
Vlookup Assistant.xlsm (193.9 KB)
Update: The Create macro has been updated to handle scenarios where the lookup column is NOT the first column in the table array. See the Updates section below for details.
A Faster Way to Write Lookup Formulas
In the video above I share a preview of VLOOKUP Assistant. The goal of this app is to make it faster and easier to write lookup formulas.
The app is still in early stages of development. It is written with VBA macros, and you can download the file that contains the code above.
VLOOKUP Assistant currently has two main features.
Feature #1: Create Lookup Formulas
VLOOKUP Assistant will create the lookup formulas (including error handling) for you with just a few clicks. When you run the macros you will just have to specify two inputs.
The inputs are gathered using the InputBox function in VBA.
The two inputs are:
1. The lookup value
First you are prompted to select the cell that contains the lookup value.
2. The table array
Next you are prompted to select a row in the table array range. This is the range that contains the lookup column and return column.
This is similar to selecting the table_array argument for VLOOKUP, but you only need to select one row in the range. VLOOKUP Assistant will automatically determine the rows to include table array and create the reference to the entire range.
This saves time from having to select thousands of rows of data, especially if you are doing that selection with the mouse instead of a keyboard shortcut.
The formula is created
The formula is then created by the macro and output to the active cell.
The macro determines the table_array by using the CurrentRegion property for the range that was selected in step 2. It also figures out the column index number argument based on the number of columns in the range.
The table_array range(s) are made absolute by default. This prevents the error forgetting to anchor the ranges before copying the formulas down.
The same macro is used for both VLOOKUP and INDEX & MATCH formulas. For INDEX & MATCH, the macro just creates the lookup column and return column arguments by creating references to the first and last columns in the table_array range.
The macro also has variants that add an IFERROR wrapper around the lookup formula.
This means you can create complex lookup formulas in just a few clicks.
Feature #2: Convert Lookup Formulas
The second feature is converting lookup formulas to use other functions. The app currently converts VLOOKUP formulas to use INDEX & MATCH instead.
INDEX & MATCH does have some advantages over VLOOKUP. Mainly inserting and deleting columns without worrying about errors, and doing lookups to the left. We have a separate post coming soon with more details.
So, VLOOKUP Assistant will make this conversion with the click of a button.
- You simply select a cell that contains a VLOOKUP formula and run the macro.
- Then run the macro which extracts the range references in the VLOOKUP arguments, and uses them to create an INDEX & MATCH formula.
- The new formula is displayed in a message box, and also copied to the clipboard. You can then paste it to the active cell.
The copy to clipboard is used to retain the Undo History. If the macro were to input the formula directly then you would not be able to undo, and potentially lose the previous VLOOKUP formula that was there before.
We can also add code to paste the new formula into the active cell. That part is manual for now.
Who is it for?
I believe this tool will be useful for a few different types of users.
If you are someone that writes lookup formulas everyday, then you know how time consuming they can be to write. VLOOKUP Assistant will allow you to create formulas quickly.
You can also use keyboard shortcuts to run the macros and select the ranges. Everything can be done without the mouse, if that's your style. 🙂
If you don't write lookup formulas often, then it can also be challenging to remember all the function arguments. You might use our VLOOKUP Guide as a cheat sheet, or do a Google search every time you need to write a lookup formula.
So, this tool will walk you through the process and write the formulas for you.
Use & Installation
The app is currently a set of macros. You can download the file above that contains the macros.
Test in the Example Workbook
There are buttons on the sheets that will allow you to run the macros within the example workbook to test them.
Add to Your Personal Macro Workbook
You can also copy the code modules to your Personal Macro Workbook. Then create macro buttons on a custom ribbon tab to make it easy to run the macros on any open file. See video #3 on this page for instructions.
This setup will allow you to run the macros on any workbook you have open.
Here is an example of the custom Ribbon tab that I created from the Customize Ribbon menu in Excel Options.
We can eventually turn this into an add-in that anyone can download and install on their computer, without having to know VBA. Similar to our free List Search Add-in.
As I mentioned in the video, VLOOKUP Assistant is in a really early preview stage. I'm publishing it now to get your feedback.
Do you think this would be useful to you?
Do you have any suggestions for additional features?
Please leave a comment below with your answers.
Important note: The macros are only going to work in limited scenarios. They have not been heavily tested yet, so keep your expectations low. 😀
Here is a list of features I'm planning to add, and your comments below will help shape this list.
- Work with Tables and create Structured Reference Formulas.
- Create lookup formulas to other workbooks. Currently only works with other sheets.
- Support for Named Ranges.
- Compatible with other language versions of Excel. Currently English only.
- Allow IFERROR to return something besides blank.
- Create Index & Match formulas that look to the left (added 8/22/19)
- Output the formula to a userform where additional options could be applied to the formula. Toggle IFERROR on/off, use COLUMNS (added 8/22/19) or MATCH in col_index_num of VLOOKUP, IFERROR return value, etc.
- Handle strings in the formula that have parentheses.
- Output the formula to a userform instead of the clipboard.
- Convert other types of formulas.
Updates & Fixes
Here is a video to explain updates & fixes I published on 8/22/2019.
Here is a list of the updates & fixes:
- Fixed bug when the lookup column was not the first column in the data range (VBA > CurrentRegion). You can now select a lookup column (table_array) that is in the middle of the data range.
- Added a new feature to use the COLUMNS function for the column index number argument in VLOOKUP formulas. This prevents errors when adding or deleting columns. See my bonus videos on VLOOKUP for more details on this technique.
- Add a new feature to create INDEX & MATCH formulas that look to the left. The process of selecting the lookup/return range in step 2 is the same. The macro just reverses the range references in the formula arguments.
So, please leave a comment to vote for any of these features or suggest other features.
Thank you! 🙂