How to Compare Hotel Guest Lists with Fuzzy Matching in Excel Using Power Query
Question details
The user needs to compare an initial hotel guest list with a master list where names may have spelling variations, cancellations, or missing entries, and exact matching formulas are unreliable.
- Product
- Microsoft Excel (Power Query); WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Reconciling hotel guest lists by matching similar names between two lists to find differences, cancellations, or missing guests.
- Observed behavior
- An exact IF or lookup formula does not reliably identify names that are similar but not identical, so fuzzy matching is required.
Prepare both guest lists as Excel tables with clear, consistent column headers, and decide which list is the master reference. If the names are imported from a hotel system, remove extra spaces and standardize capitalization with TRIM, CLEAN, and PROPER before running Power Query.
Use Power Query Fuzzy Merge to Match Similar Guest Names
Use this when the two guest lists contain spelling variations, typos, or abbreviated names and you need approximate name matching.
Power Query's fuzzy merge compares the text in two queries and returns likely matches even when spelling, typos, or name order differ. It is the most reliable way to find approximate matches without writing custom VBA.
Click inside the initial guest list, select Data > From Table/Range. In the Power Query Editor, use Home > Close & Load To > Only Create Connection, then repeat for the master guest list.
In the Power Query Editor, select one connection, click Home > Merge Queries > Merge Queries as New, and choose the second query and the guest-name column in each table.
In the Merge dialog box, check Use fuzzy matching. Set the similarity threshold to 0.80 to 0.90 and expand Fuzzy matching options to ignore case or match by text length as needed.
Click the expand icon on the merged column, select the master-list columns you want to bring in, and click OK. Review rows where one initial name matches several master names before loading to Excel.
Use XLOOKUP or COUNTIF When Only Exact Matches Are Needed
Choose this method when both lists have already been cleaned and you only need to find identical names or missing exact entries.
Review Excel Guest-List Workbooks in WPS Office Free
WPS Office is a free, lightweight Microsoft Office alternative that opens and edits Excel guest-list workbooks with full .xlsx compatibility. If your core workflow is exact lookup, duplicate checking, and sharing the results of a Power Query fuzzy merge, WPS Office gives you a familiar spreadsheet interface and saves you from a Microsoft 365 subscription.
- 1. Download and install WPS Office: Go to the WPS Office website, download the free version for Windows, macOS, Android, or iOS, and install it.
- 2. Open the Excel workbook: Launch WPS Office, click Open, and select the .xlsx workbook that contains the guest-list comparison result.
- 3. Review exact-match columns: Use the Formulas tab to insert COUNTIF or XLOOKUP, or use Data > Filter to inspect exact matches and missing names after the Excel fuzzy merge.
Frequently Asked Questions
Can WPS Office run Power Query fuzzy matching?
WPS Office Spreadsheets does not include the same Power Query fuzzy merge interface as Microsoft Excel. Run the fuzzy merge in Excel, save the workbook as .xlsx, and then open it in WPS Office for review or sharing.
What similarity threshold should I use for hotel guest names?
Start with 0.80 or 0.90. A lower threshold catches more spelling variations but creates more false matches, so always review common names manually.
Can COUNTIF or XLOOKUP match similar but not identical names?
No, COUNTIF and XLOOKUP only work with exact text. Use TRIM, CLEAN, and PROPER to normalize names first, and use Power Query fuzzy matching for approximate comparisons.
How do I avoid incorrect fuzzy matches for common names?
Keep the master list as the reference, expand the merged column in Power Query, and add a Review column. Verify ambiguous matches against booking IDs, room numbers, or dates before applying changes.
Is the final workbook compatible with WPS Office?
Yes, .xlsx workbooks created or edited in Excel are compatible with WPS Office, including formulas, tables, filters, and formatting.
