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
Video Tutorial
Download the Example File
You can download the sample file that contains the code below.
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.
Frequent 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. 🙂
Occasional users
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.
What's next?
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.
Create Formulas
- 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.
Convert Formulas
- 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! 🙂
This is great. Although I do use index/match, it is not every day, and at times I need to read up again just to ensure I use the correct parameters. So this add in would cut down on my “creating formulas time” :).
Keep up the great Excel tips / tricks etc.
The first thing I did was to format the data as a table and when I did the first lookup it fill in the entire table with the emails. I’m not near as advanced as some of the other users that are laeving comments. This is a tool my constituents will use very often for creating mailing lists and merging documents.
Hey Jon,
I’ve only begun to play around with the VLOOKUP ASSISTANT, but I can see that it is a powerful time saver, as well as a frustration-saver.
Thank you so much for providing such useful, amazing tools!
Just fiddeling around with the example file. Seems to me very useful and powerful.
As a addInn it will helps tonnes of users
This is awesome, thank you for this. I have had it on my project list to create a macro similar to this for a while now, just can’t seem to find the time. This fits the bill for my end users that aren’t good with formulas. I really love the feature to change VLookup to Index Match, that is going to save me a lot of time in the future. The only feature I can think that would be a useful addition would be an option for “Index Match Match” that will not only pull a value based on the match of a row but will also match the column names as well from an entire array; that is what I use most of the time to make it more dynamic for me and my users.
Hi Jon,
please find my +1 to Harold’s idea to add Index-Match-Match option to your great tool, as I used to use it quite pretty often as well.
I’ve even created a simple add-in based on userform with four RefEdit entries, which is working like a charm, saving a lot of my time:)
Kind regards,
Kris
Very nice!
1. While watching the video I very much liked the idea of using copy to clipboard to retain the Undo History for the VLookup conversion to Index Match. That might be also useful for the formula creation macro. (Perhaps combined with send keys CTRL+ALT+V & F & ENTER.)
2. The VLookup conversion seemed not to work with the VLookup + columns as iColIndex is not Long in this cases.
3. While trying it out, the speed of building the formulas was impressive – and I am not slow at building them by myself. So this application makes definitely sense. But when I needed to run several tests (I added $ before sLookupValue to fixate the ID column and I changed in m_VA_Convert the comma in sIndexFormula and sFinalFormula to semi colon) it felt as if it might be better to speed up things by bringing the definition of rLookupValue and rTableArray into one combined InputBox. One click less is not a lot quicker, but noticeable when you do it again and again.
4. There are 8 cases considered in m_VA_Create and you would need 8 buttons to select them. The number of buttons could be reduced by bringing selections to the userform itself. E.g. a radio button for IfError reduces the number of buttons to 4. Also other selections could be done on the userform (perhaps with changeable default settings).
Just tried it. Awesome. Thanks for this very useful app.
Hi Jon:
This is great; a huge time saver. Thanks!
Only question has to do with XlookUp. I just read somewhere that the Excel Team is creating a new function called XlookUp which essentially combines the best of Vlookup and Index/Match. Maybe that will make your efforts extraneous?
Anyway I have been following you for a long time.
Thank you for all you do!
Hi Jon, would it be possible to extend the tool to do lookups for multiple arguments. Jörg
Great suggestion, Jorg! Yes, it would be possible. I’ll add this to the list for future features. Thanks!
Nice idea Jon (and your usual great presentation with crystal clear examples!!) Just want to be sure you include features to Index-Match ABOVE as well as 2-WAY Lookups. Can do?
Thanks Bob! Great suggestions! I’ll add those to the list for future features.
Jon, this is a great tool for anyone using Vlookup or Index Match formulas. Thanks for sharing this tool/ macros. Can you please also share the macros for new excel Xlookup function and the conversion from Index Match/ Vlookup to Xlookup formulas?
Hi Jon,
I noticed in your XLookup video and it is the same here that the dialogue box for the lookup value show $B$4 but the resulting formula showing B4.
(For a vertical lookup $B4 would probably be what I use most often, I would seldom use the $B$4)
When are you going to provide us with a Vlookup addin, this will make life so easy for us rookies. Waiting eagerly.
Hi John,
May i know if the vlookup assistant add in is still free? Problem is i could not download via the link in your video on xlookup.
Can you please advise?
Very interesting
Hi Jon,
I’m amazed with all the info I’ve found here, thanks a lot. I have an advanced calculation to do and I haven’t found the solution, maybe you can give me a hand.
I have a huge amount of data to map, with 3 different variables.
In the sheet where I have the data I need to map is organized as follows:
A3:A135 Markets
B3:B135 Channel
by segments from C3:BJ135
And the sheet where I need to do the mapping it’s organized as follows:
A3:A690 Markets
B3:B690 Segments
by Channel D3:K590
Any suggestion how can I do the mapping, as it has 3 variables and not in the same order, from sheet 1 to sheet 2 it’s changing channel from columns to rows and segments from rows to columns.
Thank you very much in advance.
The Vlookup to index match is not working. It produces a bug error message. Do you have a fix for this?