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.

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 click 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

Adjust your options on the Vlookup Wizard pane:
VLOOKUP Formula Wizard for Excel.

  1. When you start the add-in, the first field will be already filled in: the utility will select the range with your main table—the table into which you want to pull matching values from the lookup table.

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

    Note. If you have more than one header rows, click on that "1 header row" phrase and enter the correct number of lines with labels:
    Enter the number of header rows.
  2. To get the whole table selected automatically, click on the Expand selection icon. To change the range, click the Select range icon or correct the address manually.
  3. Specify your Key column, the column containing the values to look up—it will help the add-in find the same records in both sheets in order to bring the relevant values from the lookup table.
  4. Lookup table is the table that contains the values to retrieve as well as the key records. Click on this box, switch to the necessary sheet, and pick the range.
    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.
  5. 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.
  6. Return column is the column with values you want to bring into your main table as a result.
  7. Opt for Use XLookup instead of Index & Match to get XLOOKUP formulas instead of INDEX/MATCH created.
  8. The Back up the worksheet option is selected by default. Since there is no technical possibility for add-ins to cancel changes and Excel's built-in Undo option doesn't work for 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.
Tip. Take advantage of the hint available in the tool. To see the hint, click on the icon in the top left corner of the Vlookup Wizard window:

Open VLOOKUP Wizard hint.

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

Responses

Kimberly Hargis says:
December 18, 2021 at 9:49 pm

My Ablebits doesn't have " XLookup instead of Index & Match". What is Xlookup and how do I update to get this feature?

Hello Kimberly,

Thank you for contacting us. To learn more about Xlookup, please have a look at our detailed blog post:
Excel XLOOKUP function with formula examples

Also, make sure to download and install the most recent version of Ultimate Suite using the link below:
https://www.ablebits.com/files/get.php?addin=xl-suite

Then run VLOOKUP Wizard and check if the Use XLookup instead of Index & Match option appeared in the pane. Thank you.

Kimberly Hargis says:
December 19, 2021 at 9:01 pm

Hi, Is it possible to look up several columns on once and have several columns of returns? For example: I have columns A, B, C and I need to find the matching values in column G for each columns then have the results for each column instead of doing each column separately. Thank you

Hi Kimberly,

Sure, I can find a solution for you. Yet, I need to know how many sheets you have and the way your data is arranged. Please send me a small sample workbook to support@ablebits.com with: 1 - your source data and 2 - the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note! The result sheet is of great importance and often gives us a better understanding of your task rather than any text description. Please don't forget to include it. Thank you.

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.

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

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

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 support@ablebits.com.