Analistable

Alternatives to the Ablebits Merge Tables Wizard

By the Analistable team · Updated · 6 min read

Ablebits' Merge Tables Wizard (part of its Ultimate Suite, with a separate add-in for Excel 365) updates a main table with matching data from a lookup table through a step-by-step wizard. If you'd rather not install an add-in, XLOOKUP covers simple one-column matches, Power Query Merge Queries gives a repeatable join with any join type, and Analistable joins sheets from Excel and Google Sheets in the browser without installing anything.

Part of our guide: How to merge Excel files: every method compared

What the Merge Tables Wizard does

According to Ablebits' documentation, the wizard matches rows between a main table and a lookup table on one or more key columns, then updates existing columns or adds new ones in the main table, with options to highlight the changed rows. Tables can be in the same or different workbooks. It's sold as part of a paid suite with a free trial.

The alternatives compared

Ways to merge two tables by key
Ablebits wizardXLOOKUPPower Query MergeAnalistable
Install requiredAdd-inNoNo (built in)No (browser)
Match on several columnsYesYes (with & or arrays)YesYes
Returns every matchOptionsFirst match onlyYesYes
RepeatableSaved scenariosLive formulasRefreshRe-run the question
Joins Excel with Google SheetsNoVia exportVia exportYes
Data leaves your computerNoNoNoNo
CostPaidIncludedIncludedFree tools; account needed for AI questions

Ablebits' feature set changes over time; check their current documentation before deciding.

When each makes sense

  • Stay with an add-in if you merge tables daily on Windows and prefer a guided dialog with highlighting.
  • XLOOKUP for one or two columns where each key has one match: =XLOOKUP(A2, Lookup[ID], Lookup[Price], ""). See VLOOKUP vs XLOOKUP vs INDEX MATCH.
  • Power Query Merge for a repeatable join you refresh each month, including anti joins to find unmatched rows. See joining two tables in Excel.
  • Analistable when the tables are spread across Excel files and Google Sheets, or when you want to ask a question of the joined data (“which customers bought less this quarter?”) rather than build the merge yourself.

Comparing the related Ablebits tools for combining sheets? See the best tools to merge Excel files.

Example: updating prices from a supplier list

Main table updated from a lookup table on SKU
SKUProductOld priceSupplier priceAction
AB-100Desk120125Price updated
AB-101Chair4545Unchanged
AB-102Lamp18Not in supplier list

Any of the methods above produces this. In Excel, =XLOOKUP(A2, Supplier[SKU], Supplier[Price], "") fills the Supplier price column, and a second column flags the difference with =IF(D2="", "Not in supplier list", IF(D2<>C2, "Price updated", "Unchanged")).

To finish the update without an add-in, copy the Supplier price column and use Paste Special → Values over Old price only for the rows marked “Price updated” (filter on the Action column first). The supplier list should also be checked the other way round: SKUs the supplier lists that are missing from your main table are new products you may want to add.

Recreating the wizard's options without an add-in

Ablebits describes options such as updating values, adding columns from the lookup table, adding non-matching rows and highlighting changes. Each has a built-in equivalent, although you assemble it yourself:

Wizard-style options and their built-in equivalents
What you wantBuilt-in approach
Fill a new column from the lookup tableXLOOKUP in a new column, or Power Query Merge then expand the column
Overwrite old values with new onesXLOOKUP into a helper column, then Paste Special → Values over the original
Update only cells that are empty=IF(C2="", XLOOKUP(A2, Lookup[SKU], Lookup[Price], ""), C2)
Highlight changed rowsConditional formatting with a formula such as =$D2<>$C2
Add rows that exist only in the lookup tablePower Query Merge with Right Anti, then Append the result to the main table
Match on two or more columnsConcatenated keys in XLOOKUP, or select several columns in Power Query Merge
Bring back every match, not just the firstFILTER (Excel 365), TEXTJOIN for one cell, or a Power Query Merge

Step by step: matching on two columns with XLOOKUP

When a product is identified by SKU and size together, join the two keys on both sides with a separator so “AB-1” + “10” can't collide with “AB-11” + “0”:

=XLOOKUP(A2 & "|" & B2, Lookup[SKU] & "|" & Lookup[Size], Lookup[Price], "Not found")

  1. Convert both ranges to tables with Ctrl+T so the formula expands with new rows.
  2. Clean the keys first: TRIM removes stray spaces, and numeric sizes stored as text won't match sizes stored as numbers.
  3. Enter the formula in the first row of the main table; Excel fills the column automatically.
  4. Filter the result column on “Not found” to review every row that didn't match before you trust the update.

XLOOKUP needs Excel 2021, Excel 365 or Google Sheets. In Excel 2019 and earlier, use INDEX with MATCH on a helper column that holds the combined key. The comparison of VLOOKUP, XLOOKUP and INDEX MATCH covers the differences.

Step by step: the same update in Power Query

  1. Select the main table and choose Data → From Table/Range; repeat for the lookup table. Close & Load each as Connection Only.
  2. Choose Data → Get Data → Combine Queries → Merge. Put the main table on top and the lookup table below.
  3. Click the SKU column in each (Ctrl+click to add Size as a second key, in the same order in both tables) and choose Left Outer.
  4. Expand the new column and tick only Price. Add a custom column such as if [Lookup.Price] = null then [Price] else [Lookup.Price] to keep the old price where there's no match.
  5. Close & Load. Next time the supplier sends a list, replace the lookup data and choose Refresh All.

Unlike the wizard, Power Query writes the result to a new table rather than editing the original in place. Many people prefer that, because the source data stays untouched and the update can be repeated. More on join kinds in Power Query Merge Queries.

Mistakes to avoid when switching

  • Assuming one match per key. If the lookup table has two rows for the same SKU, XLOOKUP silently takes the first. Check with =COUNTIF(Lookup[SKU], A2) before trusting the result.
  • Overwriting the original too early. Keep the lookup result in a helper column until you have reviewed the changes.
  • Forgetting rows only in the lookup table. A lookup fills in the main table; it never adds new rows. Run a separate check for those.
  • Leaving formulas pointing at another workbook. If the lookup table lives in a separate file, the links break when it is moved. Paste values or use Power Query, which stores the path in one place.

Frequently asked questions

Is there a free alternative to Ablebits Merge Tables Wizard?
Yes. Excel's Power Query Merge Queries and XLOOKUP are built in, and browser tools can join tables without installing anything.
Does Power Query do the same as the Merge Tables Wizard?
It joins tables on key columns with a choice of join type and refreshes the result. It doesn't highlight updated cells in the original table.
Can I merge tables from Excel and Google Sheets together?
Yes, by exporting the Google Sheet first, or by connecting both in Analistable.
How do I highlight rows that changed after a merge without an add-in?
Keep the old and new values in adjacent columns and add a conditional formatting rule with a formula such as =$D2<>$C2, applied to the whole table.
Can XLOOKUP match on more than one column?
Yes. Concatenate the keys with a separator on both sides, for example A2&"|"&B2 as the lookup value and Lookup[SKU]&"|"&Lookup[Size] as the lookup array.

Related guides