Match and Merge Data in Google Sheets

Matching and merging combines records from different ranges or sheets based on a shared value. Google Sheets supports exact lookups with formulas, and Apps Script can handle custom matching logic. When values do not match exactly, fuzzy matching can help you find likely matches for review.

Exact matching with formulas

For exact matches, use XLOOKUP, VLOOKUP or INDEX and MATCH. These work well when ids, emails or codes are consistent across sources. Clean and standardise values first to improve match rates.

Match and merge with Apps Script

Apps Script is useful when you need to join ranges, keep multiple columns or write a review output. The example below builds a lookup map from one range and merges matching values into another.

function matchAndMerge() {
  var ss = SpreadsheetApp.getActive();
  var left = ss.getSheetByName("Left").getRange("A2:B").getValues();
  var right = ss.getSheetByName("Right").getRange("A2:C").getValues();
  var map = {};
  right.forEach(function (r) {
    var key = String(r[0] || "").trim().toLowerCase();
    if (key) map[key] = r;
  });
  var out = left.map(function (r) {
    var key = String(r[0] || "").trim().toLowerCase();
    var hit = map[key];
    return hit ? [r[0], r[1], hit[1], hit[2]] : [r[0], r[1], "", ""];
  });
  var sheet = ss.getSheetByName("Left");
  sheet.getRange(2, 3, out.length, 2).setValues(out.map(function (r) { return [r[2], r[3]]; }));
}

Adjust sheet names and columns for your data. Write unmatched rows to a review area if you need to investigate missing matches.

Fuzzy matching with Flookup custom functions

When names or text values differ by spelling, spacing or abbreviations, exact lookups may miss valid matches. Flookup custom functions such as FLOOKUP can return the closest match and similarity score, and SHEETMERGE can merge related rows based on matching criteria. Review fuzzy matches before treating them as correct, especially for important records.

You can also ask the Sheets AI Assistant for a formula suggestion based on your lookup and return ranges.