VLOOKUP Assistant – Preview

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

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

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.

VLOOKUP Assistant - Create Formulas in 2 Steps

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.

VLOOKUP Assistant - Create Formulas - Step 1 - Select 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.

VLOOKUP Assistant - Create Formulas - Step 2 - Select Table Array

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.

VLOOKUP Assistant - Create Formulas - Insert Formula in 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.

VLOOKUP Assistant - Create Formulas - Features

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.

VLOOKUP Assistant - Create Formulas - Index Match Formulas

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.

VLOOKUP Assistant - Convert VLOOKUP to INDEX MATCH

So, VLOOKUP Assistant will make this conversion with the click of a button.

  1. You simply select a cell that contains a VLOOKUP formula and run the macro.
  2. Then run the macro which extracts the range references in the VLOOKUP arguments, and uses them to create an INDEX & MATCH formula.
  3. 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.

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Here is a list of the updates & fixes:

  1. 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.
  2. 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.
  3. 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! 🙂

121 comments

Your email address will not be published. Required fields are marked *

  • When are you going to provide us with a Vlookup addin, this will make life so easy for us rookies. Waiting eagerly.

  • 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)

  • 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?

  • 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?

  • 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!

  • 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).

  • 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

  • Just fiddeling around with the example file. Seems to me very useful and powerful.
    As a addInn it will helps tonnes of users

  • 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!

  • 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.

  • 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.

  • Like this idea alot. Any reason why you wouldn’t include the “$” in front of the ID reference for the Vlookup function –> ie =vlookup($A1,…..) ? I’m thinking theoretically if you were copying the lookup formula over, you would not want to lose the column reference to your ID column.

    Also would be nice to have the option to make the vlookup column reference dynamic (maybe using the column function).

  • Looks great so far. Most of the data I work with are in table format, so am looking forward to that enhancement to this future Add-In.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly