Description

The Excel LOOKUP Wizard AddIn is a simple step by step tool to allow you to lookup and copy matching columns from another Excel Workbook or spreadsheet within the same workbook. The VLOOKUP formula is one of the most often used functions in Excel for finding a matching value of a corresponding column based on an index column (i.e. unique ID). In many cases you only want to do this exercise once – simply column matching columns and that is it. Why waste your time when you can use the AnalystCave Excel LOOKUP Wizard!

Excel LOOKUP Wizard Example

The LOOKUP Wizard allows you to copy columns corresponding with the matched value from your LOOKUP column. Below you can see the LOOKUP Wizard in action on an example table of product IDs which I wanted to match with corresponding columns.

LOOKUP Wizard Features

The AddIn is very flexible and support a variety of different scenarios. Below is a summary of the key features:

Feature	Supported
Looking up data from external Excel files	Yes ✅
Looking up data from the same Workbook	Yes ✅
Getting data from a simple Excel Range	Yes ✅
Getting data from an Excel Table	Yes ✅
Requires lookup column to be on the left of columns to be copied	No ❎
Requires installation	No ❎

The AddIn allows you to save a lot of time even when compared to using a VLOOKUP formula. The VLOOKUP function will require that the lookup column be on the left of the columns you want to get. Meanwhile the AddIn is flexible and allows you to get any columns as long as the lookup column in the row will match.

Step by Step Example

Prepare

The example provided assumes:

• You have a destination table – a table (not necessarily an Excel Table, can be a range as well) that at least contains your lookup column i.e. the column with unique values which you want to use to associate and copy values from your source table
• You have a source table in the same or another Excel file – the source table is a table (Excel Range or Excel Table) that contains the same lookup column (can have more or less items) and additionally corresponding columns you will want to copy back to the destinations table

Run the LOOKUP Wizard

To start using the AddIn simply Un-Zip it (right-click and select Extract All…). Once you open it you should see a familiar new Ribbon group:

Usually it should appear after the View Ribbon group. To use the AddIn simply select the LOOKUP Wizard button (see image on the right). The LOOKUP Wizard is a VBA (Macro) powered AddIn hence it runs on your Excel engine and does not require any additional packages or installation. Clicking on the AddIn button will open the Wizard window.

In this example I assume the table on the left is my destination table and the table on the right is my source table.

Once you run the Wizard you will see the screen below.

Configure the destination table fields

On the Wizard screen you will have to configure a few fields – which takes only a couple of seconds and you should be guided by the tables on the left. Below you can find a detailed reference of the fields:

Field	Definition
Destination Worksheet	This is the worksheet to which you want to copy the data from your source table. This table and the source table should have one common lookup column. The destination worksheet needs to be in the currently active workbook
Lookup Table Fields
Select File	Once your destination worksheet is selected you can click Select File to select the source workbook. Once selected it will show under File Name
Worksheet	The source table worksheet
Lookup Column	A drop down list containing all columns in the source table. The lookup column name must match the same name in the destination table.
Result Columns	Select any number of columns you want copied to the destination table from the list

See the example completed Wizard form in the image below. Notice that as you run through the fields of the Wizard the columns of the tables on the left with change their red markings to reflect which sections the fields are addressing.

Run… and let the magic happen

Once ready click Copy Data and the lookup operations will start running. You should see the progress within the wizard itself. The selected Result Columns will get copied back into your destination table. The Add-In opens the source workbook in the background as read-only. This means that you can don’t have to close either workbook during this operation. See the result basis the example:

Excel Table Detection

What is great about the Add-In is that is supports both Excel Tables and regular Worksheets (provided header in first row), regardless if they are in the source or destination worksheets. The wizard will let you know if it identified an Excel Table with these checkboxes:

This makes your life easier as you don’t have to create Excel Tables just to justify the usage of the Add-In. Keep the data in the format you like.

Enable/Install

As the LOOKUP Wizard is a XLAM file (Excel 2010 and above AddIn format) it requires no installation! You can use the LOOKUP AddIn it in 2 ways:

• Enable on demand – simply open the AddIn to enable it when needed
• Enable it Always – if you want the AddIn to always be available simply put the AddIn in C:\Users\[your.username]\AppData\Roaming\Microsoft\Excel\XLSTART where your.username is you workstation username

Trouble shooting

Wizard Window starts shrinking

On some versions of Excel the AddIn window might randomly start shrinking (might be in cases where multiple screens are attached). To prevent this:

• Go to File
• Click Options
• Go to the Generalsection
• Select Optimize for compatibility

See below the Options screen mentioned:

Add-In not working

Be sure that both worksheets correctly structured. In case either contains merged cells the Add-In might not work as it will not know to which column the merged cell applies to. Keep column names unique and be sure to have the lookup column name identical across both worksheets.

Have questions or issues with purchasing/downloading the AddIn? Write directly to me at analystcave(at)gmail.com!