How to Combine Two Columns in Excel into a Match Key That Reconciles

How-to · Updated July 2026 · ~17 min read

The bank statement row reads 25/08/2026, MARLOW LTD 0803, -4,820.00. The cash ledger row for the same payment reads 2026-08-25, Marlow Limited, 4,820.00 in a debit column, reference 1043. One transaction, and not a single column agrees with its opposite number. So the XLOOKUP returns #N/A on all two hundred rows and somebody spends the afternoon reading a statement line by line.

The fix is a match key: one helper column on each sheet, joining two or three fields into a single string that both systems can be made to produce identically. Mechanically it is the ordinary =A2&"|"&B2 that every "combine two columns" tutorial teaches. What decides whether it works is everything you do to the fields before the join — converting the date to a fixed text format instead of a serial number, taking the absolute value of the amount to a fixed two decimals, padding the reference back to four digits, and stripping the trailing space and the non-breaking character that every ERP export ships. Skip those and the key looks perfectly correct in the cell and matches nothing.

This post builds it end to end on real ledger data: the ampersand version, the TEXTJOIN version and the one argument everybody sets the wrong way round for a key, the four traps that break it silently, and the Power Query version that rebuilds itself when next month's export lands and returns the unmatched rows as a table. It is also, along the way, the plain answer to how you combine two columns with a space between them — that is the same formula with a different separator.

One scope note before starting. This post is about combining two columns inside one sheet. Stitching several exports or workbooks into a single table is a different job with different tools — that walkthrough is merging multiple Excel files into one. And Merge & Center (Home tab, Alignment group) is neither job: it merges cells for layout and keeps only the upper-left value, deleting everything else. Excel even warns you. Never point it at data.

Four things that silently break a match key

None of the four announces itself. The key sits in the cell looking exactly the way you intended, the formula bar shows nothing odd, and the lookup against the other sheet returns #N/A anyway. Two of them are inherent to concatenation; two only bite when you are matching one system's output against another's.

Dates become serial numbers

Excel stores a date as a count of days since 1 January 1900; the format you see — 25/08/2026 — is a display layer on top of the stored number, which for that date is 46259. The & operator, and every function in this post, reads the stored value, not the display. So ="Paid "&B2 produces Paid 46259, and it will do so identically in CONCAT and TEXTJOIN.

The fix is to convert the date to text yourself, in the format you choose, before joining:

="Paid "&TEXT(B2,"dd/mm/yyyy")

TEXT(value, format_text) takes the same format codes as Format Cells, so "dd mmm yyyy" gives 25 Aug 2026 and "mmm-yy" gives Aug-26 for period labels.

Leading zeros vanish

If your account codes, cost centres or employee IDs start with zero — 0042, 0300 — and the column is right-aligned, Excel is storing them as numbers. The zero you see comes from a custom number format such as 0000; the stored value is 42. Concatenate it and you get 42, because again the formula reads the value, not the format.

Same fix, with a digit mask instead of a date mask:

=TEXT(A2,"0000")&" - "&B2

TEXT(42,"0000") returns 0042, so this also repairs codes whose zeros were already stripped at import — provided every code is the same width. If your codes are stored as text (left-aligned, often with a green error triangle), the zeros are genuinely in the cell and pass through & untouched; no fix needed. The deeper cure is importing code columns as text in the first place, which is covered in cleaning messy data in Excel.

The amount: signs, split columns and unrounded pence

An amount is the strongest field in a match key — two systems rarely disagree about how much money moved — and it is the field people put into the key raw, which breaks it in three ways at once.

The sign is a presentation choice, not a fact. The bank statement shows a payment as -4,820.00; the cash book has 4,820.00 sitting in a credit column with the sign implied by which column it is in; a general ledger export may carry separate Debit and Credit columns, both positive. Join the amount as it stands and the two keys differ by a minus sign on every payment in the file.

The decimals are the subtler half. An amount that was calculated rather than typed — a VAT split, an FX conversion, an allocation — can carry more decimal places than the cell displays, so a cell reading 142.60 may actually hold 142.599999999999. It concatenates as everything it holds. The other sheet, where the same figure was typed, produces 142.6. Two strings, one amount, no match.

Both go away with one wrapper, which takes the absolute value and rounds the string to two decimals:

=TEXT(ABS(D2),"0.00")

Where the ledger splits debits and credits across two columns, add them first — =TEXT(ABS(D2+E2),"0.00"), one of them being blank on any given row — and keep the sign out of the key entirely. If direction matters to you, put it in the key as its own field ("DR" or "CR") where you control it on both sides, rather than leaving it inside a number.

Invisible characters: trailing spaces and CHAR(160)

The last trap is the one that produces the most wasted time, because the evidence is that the two cells are identical. Marlow Limited and Marlow Limited look the same in a column, in the formula bar and side by side on screen. They are different strings, so the keys built from them are different strings.

Two culprits. A trailing or double space, which TRIM removes — it strips leading and trailing spaces and collapses runs of internal spaces to one. And the non-breaking space, character 160, which web pages and a good many ERP and portal exports use instead of an ordinary space. TRIM does not touch it, because as far as Excel is concerned it is not a space at all. Nor does CLEAN, which removes the non-printing characters 0 to 31 and leaves 160 alone. Substitute it out first, then trim:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

To find out whether this is your problem, put =LEN(A2) beside =LEN(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) on both sheets. If the numbers differ, there is something in the cell you cannot see, and it is in your key.

Formatting of every kind is lost the same way, so here is the short table to keep:

With those four in hand, one rule before any of the methods.

One pattern, both sheets

A match key is not a formula. It is an agreement between two sheets, and it only holds if both sides are built by the same expression — same fields, same order, same delimiter, same normalising wrappers — rather than by whatever is convenient in each source's own layout.

This is where most reconciliations quietly fail. The bank export has a genuine date column, so the key gets TEXT(A2,"dd/mm/yyyy"). The ledger export arrives with dates as text in ISO form, so nobody wraps anything and the key takes the cell as it stands. Both keys look entirely reasonable in their own column. MARLOW|25/08/2026|4820.00 and MARLOW|2026-08-25|4820.00 match on nothing at all, and the lookup reports that none of the two hundred payments exists.

The two-cell diagnostic takes ten seconds. Put a known-good pair of keys side by side — one row you are certain is the same transaction on both sheets — and compare them:

=EXACT(K2,Ledger!K2)

EXACT is case-sensitive, which is what you want here: =K2=Ledger!K2 returns TRUE for keys that differ only in case, and then the lookup behaves inconsistently later. If EXACT returns FALSE and the two strings look identical on screen, compare their lengths — =LEN(K2) against =LEN(Ledger!K2) — and the difference is the invisible character you have not stripped yet.

Three practical consequences. Apply UPPER on both sides so case never enters into it. Keep the field count and the field order identical, even where one sheet has a field the other does not — leave it out of both keys rather than including it on one. And write the key formula once, then copy the cell to the second sheet and repoint the references, rather than retyping it: retyping is how "|" becomes " | " on one of the two sheets and nobody notices for an hour.

Method 1: the ampersand — the key formula, on every version of Excel

For a match key, & is usually the right answer: no version gate, no function to remember, and you can see exactly what is being joined and what each field has been wrapped in.

Here is the whole thing, with the four traps already handled — supplier name normalised, date forced to a fixed text format, amount made positive and rounded to two decimals:

=TRIM(UPPER(SUBSTITUTE($A2,CHAR(160)," ")))&"|"&TEXT($C2,"yyyy-mm-dd")&"|"&TEXT(ABS($D2),"0.00")

That builds MARLOW LIMITED|2026-08-25|4820.00, and the identical expression over the ledger's own column letters builds the same string from the other side. The pipe matters: it prevents false joins, because 12|34 and 123|4 stay distinct where 1234 would collide. Pick a character that cannot occur in the data — a pipe or a tilde, not a hyphen or a space, both of which turn up inside supplier names and references.

Use yyyy-mm-dd rather than dd/mm/yyyy inside a key, for two reasons: it sorts correctly as text, and it is unambiguous, so a key you look at in six months cannot be read the American way round. The format only has to be consistent, but there is no reason to pick the one that can be misread.

A COUNTIF on that column also finds double-entered invoices even when no single column repeats, which is the second job the key does; what to do once you have found them is removing duplicates in Excel.

The same operator does the simpler jobs, and they are worth having in the same file. Payroll full names, first name in A, surname in B, with a space between:

=A2&" "&B2

That space is a text literal in quotes — the answer to the perennial "how do I combine 2 columns with a space" question. Any literal goes the same way: ", " for comma-space, " - " for the accounting label separator.

Account labels from a chart of accounts export, code in A, name in B:

=TEXT(A2,"0000")&" - "&B2

gives 1000 - Cash, 0042 - Petty cash float, and so on down the column. Enter it in the first row and double-click the fill handle (the small square at the cell's bottom-right corner) to fill to the bottom of the adjacent data; inside a proper Excel Table it fills itself. Note the difference in intent: this one is for a human to read, so a readable separator is right. A key is for a machine to compare, so nothing in it is chosen for looks.

The ampersand's one weakness is verbosity: joining ten columns means nine &"?"& interludes, and a key with three wrapped fields is already a long line to read. That is what the next two functions are for.

Method 2: CONCAT — and why it is the wrong tool for a key

CONCAT joins everything you hand it, and unlike the ampersand it accepts ranges:

=CONCAT(A2:B2)

The catch is visible in the output: 1000Cash. CONCAT has no delimiter argument. For a label that is an inconvenience; for a key it is disqualifying, because without a separator two genuinely different rows can produce the same string — an invoice reference of 12 with an amount of 34 and a reference of 123 with an amount of 4 both come out as 1234, and your reconciliation ties two unrelated transactions together and reports no exception. You can interleave literals, =CONCAT(A2," - ",B2), at which point the ampersand was shorter. CONCAT earns its keep when you have many contiguous text columns and genuinely want no separator — rebuilding a reference string that an export split, say — and never for matching.

Two version notes. CONCATENATE is the legacy version — Microsoft keeps it for compatibility with old workbooks, but it does not accept ranges and there is no reason to use it in new work. And CONCAT itself (with TEXTJOIN, next) shipped in Microsoft 365 and, as of 2026, is present in the Excel 2019, 2021 and 2024 perpetual releases — but not in Excel 2016 perpetual, despite 2016 being newer than CONCATENATE. On 2016, the ampersand is your everything. Availability by release does get revised, so if you are writing for a mixed-version team, check Microsoft's current function-availability page — or simply type the function on the machine in question and see whether it returns #NAME?.

All four traps from the first section apply unchanged: CONCAT reads stored values, so dates, leading zeros, signed or calculated amounts and invisible characters need exactly the same treatment as before.

Method 3: TEXTJOIN — and the one argument everybody sets backwards

TEXTJOIN(delimiter, ignore_empty, text1, …) fixes both of CONCAT's gaps at once: the delimiter is typed once and applied between every pair, and ignore_empty handles the blanks that every real export contains. For a long key it is the readable version of Method 1:

=TEXTJOIN("|",FALSE,TRIM(UPPER(A2)),TEXT(C2,"yyyy-mm-dd"),TEXT(ABS(D2),"0.00"))

Note the second argument. For a match key, ignore_empty must be FALSE — the opposite of the advice every other article gives, for a reason none of them has cause to mention. With it set to TRUE, a blank field is skipped and the remaining fields shuffle left, so a row with no reference produces a key with two segments where its opposite number produces three. Worse, two genuinely different rows can collapse onto the same string: a missing reference on one row and a missing amount on another can land the surviving fields in the same positions. FALSE keeps the empty segment as an empty segment — MARLOW LIMITED||4820.00 — which is honest about what is missing and still lines up field for field with the other sheet.

The place ignore_empty genuinely earns its keep is presentation, and the showcase is the one-line invoice address. Address 1, Address 2, City, Region and Postcode sit in B through F, and Address 2 is blank on half the rows. The ampersand version produces 12 Marina Walk, , Dubai, , 00000 — double separators wherever a field is empty. TEXTJOIN with ignore_empty set to TRUE skips them:

=TEXTJOIN(", ", TRUE, B2:F2)

gives 12 Marina Walk, Dubai, 00000 on the sparse rows and the full five-part address where everything is present. No IF ladder, no cleanup pass.

Details worth knowing before you rely on it:

• A space is not empty. ignore_empty skips truly empty cells and cells whose formula returned "" — but a cell containing a single space is text, and TEXTJOIN will faithfully join it, giving , , back again. If an export pads empty fields with spaces, run TRIM first (or wrap each argument), part of the wider cleanup in cleaning messy data.
• The traps still apply. A date column inside the range comes out as serial numbers. Mix TEXT() into the argument list where needed: =TEXTJOIN(" | ", TRUE, A2, TEXT(C2,"dd/mm/yyyy"), TEXT(D2,"#,##0.00")).
• There is a ceiling. A cell holds at most 32,767 characters; if the joined result exceeds that, TEXTJOIN returns #VALUE!. You will only hit it joining whole columns, but that is exactly what =TEXTJOIN(",",TRUE,A:A) does, so join ranges, not columns.
• Same version gate as CONCAT: Microsoft 365, and the 2019, 2021 and 2024 perpetual releases — not 2016 perpetual, as of 2026.

Method 4: Flash Fill — fine for a one-off, never for a key

Take the rule first: never build a match key with Flash Fill. It writes static values, so a key column built this way on one sheet and a formula column on the other drift apart the moment either export changes — and you will not see the drift, because nothing errors. You will just see fewer matches next month than last, and spend the afternoon looking for a reason on the wrong side of the file.

For everything else it is excellent. Flash Fill (Excel 2013 onwards) skips formulas entirely: you type what you want the first result to look like, and Excel infers the pattern from your example.

1. In C2, next to Sara and Haddad, type the result you want: Sara Haddad.
2. In C3, start typing the next one — after a character or two Excel usually shows the whole column as a grey preview; press Enter to accept it.
3. If no preview appears, select C2 and press Ctrl+E, or use Data tab > Data Tools group > Flash Fill.

It handles more than joining — S. Haddad from the same two columns, or extracting a code out of a combined string — because it is pattern inference, not concatenation. That is also its weakness. Three things to know:

• The output is values, not formulas. Edit a surname in B and column C does not change. Add rows and nothing fills. For a report you refresh monthly, that means redoing it monthly — use a formula or Power Query instead.
• It guesses. Give it Sara Haddad as the only example and it cannot know whether row 7's double-barrelled surname or middle name should behave the same way. Scan the filled column before trusting it, especially past the first screen.
• It needs an adjacent column in the same contiguous block as the source data, and it fills down only as far as that block runs.

For a hundred rows you need once, it is unbeatable. For anything recurring, keep reading.

Method 5: Power Query — the key, and the unmatched list, on refresh

If the same export lands on your desk every month, combine the columns in a query, so the work re-runs on refresh instead of being redone by hand. Power Query is built into Excel 2016 and later (Data tab > Get & Transform Data); if the editor is new to you, start with Power Query for beginners.

1. Click anywhere in the data and use Data > From Table/Range. Excel converts the range to a Table if it is not one and opens the Power Query Editor.
2. Click the first column header, then Ctrl-click the second — selection order controls join order, so click the code column before the name column.
3. Transform tab > Text Column group > Merge Columns replaces the two columns with the merged one. If you want to keep the originals, use Add Column tab > Merge Columns instead — same dialog, different outcome.
4. In the dialog, set Separator to Custom and type - (space, hyphen, space), name the new column Account Label, and click OK.
5. Home > Close & Load puts the result on a new sheet. Next month, paste the new export into the source table and Data > Refresh All re-runs everything.

Power Query handles the date trap better than formulas: when a selected column is typed as date, the merge step converts it to text through your locale first, so you get 25/08/2026 in the output, not 46259. The generated M shows the mechanism:

= Table.CombineColumns(
    Table.TransformColumnTypes(Source, {{"Account Code", type text}}, "en-GB"),
    {"Account Code", "Account Name"},
    Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),
    "Account Label")

Leading zeros need one deliberate step, because a code Excel stored as the number 42 arrives in Power Query as 42 — converting it to text at that point gives "42", not "0042". Re-pad it before merging with a custom column (Add Column > Custom Column):

Text.PadStart(Text.From([Account Code]), 4, "0")

Text.PadStart left-pads to the width you give it, so every code comes out four digits. If your source is a CSV that Power Query imports directly, there is a cleaner fix: set the code column's type to Text in the import step (click the type icon in the column header, choose Text, then Replace current in the dialog that appears) and the zeros never get stripped at all.

Then let the query do the matching too

Building the key in a query is only half of what Power Query is for. Build the same key in a second query over the ledger — same fields, same order, same separator, which is easier to guarantee here than in formulas because you can see both queries' steps side by side — and then merge them:

1. In the Power Query Editor, select the statement query and use Home > Combine group > Merge Queries.
2. Pick the ledger query underneath, and click the key column in each so both are selected.
3. Set Join Kind to Left Anti (rows only in first) and click OK, then Close & Load.

What loads is a table of statement lines with no ledger match — the exceptions, and nothing else. Do it a second time with Join Kind set to Inner (only matching rows) and you get the matched pairs on their own sheet, with both sides' columns available to expand.

Those two tables are the reconciliation deliverable, and the point of doing it here rather than with formulas is that they rebuild themselves. Next month, replace the source files, press Data > Refresh All, and the exception list is the exception list for the new period. Nobody re-drags a formula, and nobody forgets to extend a range.

Using the key: XLOOKUP, duplicates, and the unmatched list

With a key on both sheets, three jobs become one formula each. Say the key is in column K on both, the statement is the sheet you are working on, and the ledger sheet is named Ledger.

Pull the other side's detail onto your row.

=XLOOKUP($K2,Ledger!$K:$K,Ledger!$B:$B,"No match")

The fourth argument is what makes this usable: "No match" instead of #N/A means you can filter the column on that exact text and get your exception list without wrapping anything in IFERROR. XLOOKUP needs Microsoft 365 or Excel 2021 and later; on 2016 or 2019 the equivalent is =IFERROR(INDEX(Ledger!$B:$B,MATCH($K2,Ledger!$K:$K,0)),"No match").

Find the duplicates inside your own file.

=COUNTIF($K$2:$K$500,$K2)

Anything above 1 is the same supplier, the same date and the same amount recorded twice — the double-entered invoice that no single column reveals, because the reference was keyed differently the second time. One caveat worth knowing: COUNTIF is documented as unreliable on criteria strings longer than 255 characters, so keep keys short. Three fields is plenty; a key with the whole payee narrative in it is asking for trouble on both length and matching.

Flag what did not match at all.

=COUNTIF(Ledger!$K:$K,$K2)=0

That column is the one worth a human's attention, and it is the honest output of this whole exercise. An exact key finds exact matches — which is the right first pass, because it clears the eighty per cent that genuinely do agree once the date, sign, pence and invisible characters are dealt with, and it clears them without judgement being involved. It will always leave a residue: the payment the bank split into two lines, the supplier whose statement description never resembles their ledger name, the transaction posted a day later. That residue is the subject of fuzzy matching in Excel, and of the walkthrough in reconciling a bank statement in Excel with AI. Do not lower the standard of the key to try to absorb it — a key that matches things it should not is worse than a residue you can read.

Lock in the results before deleting the source columns

A formula in C that references A and B returns #REF! the moment you delete A and B — a classic Friday-afternoon mistake on a finished report. Convert formulas to values first: select the combined column, Ctrl+C, then right-click > Paste Options > Values (in current Microsoft 365 builds, Ctrl+Shift+V pastes values directly). Then delete the source columns. Flash Fill output is already values; Power Query output lives in its own table, so the loaded sheet is safe, but deleting columns from the source table breaks the query instead — remove them inside the editor if you must.

One rule specific to keys: never convert the key to values on one sheet and leave it as a formula on the other. The moment either source is re-sorted, re-imported or has a row inserted, the live side moves with the data and the frozen side does not, and the two stop describing the same rows. Freeze both or freeze neither.

How HISAB 360 helps

The work above is teachable, but the fifth time you describe the same normalisation — "strip the non-breaking spaces out of the payee, force the date to ISO, take the amount positive to two decimals, join them with pipes, and build the same thing on the ledger sheet" — you start wishing you could just say that to Excel. HISAB 360 is a paid Excel add-in for Windows desktop Excel (Microsoft 365 / 2016+) that puts an AI assistant in a panel inside the workbook. It reads the sheets you are looking at, so you can describe the two exports in plain English and it writes the normalised key formula into both — or, for the recurring case, builds the Power Query version: the key, the type-to-text, the Text.PadStart and the Left Anti merge that produces the unmatched list, as a query you can open, inspect and refresh next month.

Because it also connects to QuickBooks Online, Xero, Zoho Books, Odoo and FreshBooks — reading and writing — the ledger side of the match often does not need to arrive via CSV at all: it pulls invoices, bills and ledger activity straight into sheets, already typed, which removes the serial-date and stripped-zero traps before you start and usually the invisible characters with them. It is a copilot that writes the steps for you to check, not something that reconciles unattended: read the query, read the exception list, and decide what the residue means yourself. There is a 15-day free trial with 50 AI credits and no card required. Honest limits: Windows desktop Excel only — no Mac, no Excel on the web — and it is paid software after the trial, from $7/month.

Choosing a method

Whichever you choose, the four traps travel with you: TEXT() for dates and zero-padded codes in formulas, typed-as-text columns or Text.PadStart in Power Query, ABS() to two decimals for anything with a sign or a calculated pence, TRIM(SUBSTITUTE(…,CHAR(160)," ")) for what you cannot see — and a careful eye on Flash Fill's guesses.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo and FreshBooks. The 15-day trial is the full product, no card required.