How to use VLOOKUP Formula Wizard for Excel

VLOOKUP Formula Wizard is a single-step tool that quickly matches and pulls the necessary values from another Excel worksheet. It gives you the possibility to insert INDEX/MATCH or VLOOKUP formulas into your main table. There are just 5 simple fields where you specify your tables and columns, and you are a click of a button away from the results.

Video: How to build VLOOKUP formulas

Before you start

Open both spreadsheets you are going to work with: your main one where you want to insert the results as well as the worksheet that has the matching and resulting values.

Pay attention to the Back up the worksheet option. We recommend keeping it selected as Excel doesn't let you cancel changes made by add-ins.

Start VLOOKUP Formula Wizard

To run the add-in, go to the Ablebits Data tab and find the Vlookup Wizard icon in the Merge group:
Open VLOOKUP Wizard for Excel.

Tip. Select a cell in the main table before running the add-in to have the whole table highlighted automatically.

How to use VLOOKUP Formula Wizard

You can see the VLOOKUP Wizard pane:
VLOOKUP Formula Wizard for Excel.

  1. Click this double arrow to expand the pane and open the VLOOKUP Wizard help scheme:
    Expand the pane to see the help scheme.
  2. When you start the add-in, the first field will be already filled in: the utility will select the range with your main table.
    Tip. If you hover the mouse over any field in the pane, the corresponding area will be highlighted in the help scheme on the left. Text tips will make it easier for you to understand what is what:
    Select your main table where you will pull matching values to.
  3. To get the whole table selected automatically, click on the Expand selection icon.
  4. To change the range, click the Select range icon or correct the address manually:
    Select the needed range to vlookup in Excel.
  5. The Your table has 1 header row checkbox is ticked by default to help you find the right key column. If your table has no headers, uncheck this option and you will see the list of columns as they are named in Excel (Column A, Column B, etc.)
    Tip. If you have more than one header row, click on that "1 header row" phrase and enter the correct number of lines with labels:
    Enter the number of header rows.
  6. Specify your key column: it will help the add-in find the same records in both sheets in order to bring the relevant values from the lookup table:
    Select the key column to vlookup in Excel.
  7. Lookup table is the worksheet that contains the values to retrieve as well as the key records. Click on this field, switch to the necessary sheet, and pick the range with the data to look at:
    Choose the table to lookup in Excel.
  8. To get the whole lookup table highlighted automatically, select any cell inside it and then click the Expand selection icon.
  9. To change the range in the lookup table, click the Select range icon or correct the address manually.
  10. The Lookup table has 1 header row checkbox is selected by default. If your table has no headers, uncheck this option and you will see the list of columns as they are named in Excel (Column A, Column B, etc.)
    Tip. If you have more than one header row, click on the "1 header row" phrase and enter the necessary number of headers.
    Note. If the column you need to return has several headers, only the last one will be inserted into the main table. For example, if there are 3 header rows in your table, the value from the third line only will be inserted into your table in the return column.
  11. Lookup column is the one with the key records in your lookup table. The add-in will compare the key values in both sheets to be able to fetch matching values. We can use the SKU column to find the right records:
    Pick the lookup column in Excel.
  12. Return column is the column with values you want to bring into your main table as a result. As we want to get product prices, this is the column we will select:
    Choose the return column to vlookup in Excel.
  13. The Back up the worksheet option is selected by default. Since there is no technical possibility for the add-ins to cancel the changes and Excel's built-in Undo option doesn't work for the changes as well, we recommend keeping this checkbox selected. Thus, the add-in will automatically create a copy of your main worksheet before making any changes to it.

Click Insert to get the formulas that retrieve the values you need. The add-in will insert VLOOKUP or INDEX/MATCH functions depending on what your data require.


In trying to use the Vlook up wizzard, The "look up table" box will not grab the proper name of the look up sheet. It only wants to reference the main sheet. It does not pick up the "name" of the sheet just the fields of the main sheet.
YI there also is no "expand" selection" button as mentioned in the tutorial.

What am I missing


Hi Miles,
Thank you for using our product and for your comment.

It looks like you have an old VLOOKUP Wizard version. Please upgrade your Ultimate Suite for Excel using this link:

If this won’t help, please contact our support team at

Derrick Strom says:
October 18, 2018 at 4:14 pm

My lookup is returning #NA but I can manually find the value I'm searching for and the formula wizard appears to be work.


Hello, Derrick,
Thank you for your comment. I am glad VLOOKUP Formula Wizard has helped you. If you experience any problems with Excel formulas, please do not hesitate to contact us with a detailed description of your task, we’ll be happy to assist you.

Thank you.


I have Abblebits and use the vlookup wizard often. However, I would like to have the results go to an existing column instead of inserting a new column. Is this possible? if so, how?


Hello Rick,

Unfortunately, it is not possible to insert the result into an existing column in the current version of the add-in.


Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard