OmniDocMatching Data With Excel Formulas Across Columns and Sheets
Learn which Excel formula for matching data fits two columns or two sheets, with exact-match steps, TRUE/FALSE checks and fixes for common lookup errors.
You have two lists and need to know which items appear in both, or you need a price from a second sheet beside each product code. Checking by eye works for ten rows and falls apart at a few hundred. A matching formula does the comparison for every row at once, and the same formula keeps working when the source data changes.
This guide is for anyone comparing two columns, or pulling information from one sheet into another, in Excel. You will learn which formula fits which job, how to write each one, and how to check that the matches are real. The examples use simple sheet and column layouts you can copy and adjust.
Matching data with Excel formulas comes down to two tasks: a yes-or-no check that a value exists in another list, and a lookup that returns a related value. The short answer below covers both.
Which Excel formula for matching data fits your task? The short answer
Use MATCH with 0 for an exact check, wrapped in ISNUMBER for a TRUE or FALSE result, and use XLOOKUP (or INDEX with MATCH in older versions) when you need a value from another column or sheet.
| Your situation | What to do | Why |
|---|---|---|
| Check whether each value in column B appears in column A | =ISNUMBER(MATCH(B2,A:A,0)) | Returns TRUE or FALSE for each row |
| Pull a value from another sheet using a shared key | =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"Not found") | Returns the matched value and a label when nothing matches |
| Same lookup in an older Excel version | =IFERROR(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),"Not found") | INDEX and MATCH work without XLOOKUP |
| The value you need sits left of the key | INDEX with MATCH | VLOOKUP only reads to the right of its key column |
| Upper and lower case must match | =EXACT(B2,A2) | MATCH and XLOOKUP ignore case |
Replace Sheet2 with your sheet name, and put names that contain spaces in single quotes, such as 'Q3 Sales'!A:A. The formulas use whole-column references like A:A, so they cover every row, including rows you add later.
Before you start
Check these items first. Each one prevents a common cause of wrong or missing matches.
- Confirm the key columns hold the same kind of data. A number in one column and the same number stored as text in another will not match, even though both look identical. Select a cell and look at the alignment and formula bar, or use VALUE on one side to convert it.
- Look for extra spaces. A trailing space after a name is invisible but breaks an exact match. Use TRIM on a helper column if your data came from a web page or a PDF.
- Keep headers out of the lookup. Whole-column references include the header row. Make sure no header text equals a value you are searching for, or start the lookup from the first data row.
- Check for duplicate keys in the lookup list. Lookup formulas return the first match they find. If a key appears twice with different values, you will see only one of them, so decide which one should count.
- If the source is a PDF, check the numbers after export. The OmniDoc desktop client (download.omnidoc.top) can export PDF content to Excel, and it can run OCR on scanned PDFs. OCR can misread characters, so compare a few values against the original page before you match them.
Matching two columns and pulling values from another sheet, step by step
- Decide the question you are answering. Write it in one line, such as "Which order IDs in column A also appear in the returns list?" This tells you whether you need a TRUE or FALSE result or a value back.
- Clean the key columns. Add a helper column with =TRIM(A2) and use that column in the formula, or fix the source data directly so both sides use the same format.
- Write the yes-or-no check. In an empty column next to your list, enter =ISNUMBER(MATCH(B2,A:A,0)). The 0 forces an exact match, and ISNUMBER turns the result into TRUE or FALSE.
- Write the lookup for a value from another sheet. In the result column, enter =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"Not found"). The last argument is the label shown when the key is missing.
- Use the older version if needed. If XLOOKUP does not appear in your version of Excel, replace it with =IFERROR(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),"Not found").
- Fill the formula down. Double-click the fill handle or drag it to the last row of your list. Only the key cell changes from row to row, while the lookup columns stay the same.
- Add a case check only when case matters. For a case-sensitive comparison, put =EXACT(B2,A2) in another column. Use it when "Smith" and "SMITH" should be treated as different entries.
- Spot-check a few rows. Pick three rows you know should match, three that should not, and one with an unusual value, and confirm each result by hand before you rely on the whole column.
Check the result
Once the formulas fill every row, check the output for these problems before you use it for decisions.
| Check | How to see it | If it's wrong |
|---|---|---|
| Errors or "Not found" where you expected a match | Filter the result column for errors or the label | Look for trailing spaces, text-versus-number mismatches, or a wrong sheet name in the formula |
| TRUE for values that look different | Compare the two cells with =EXACT or check their lengths with LEN | Apply TRIM, or switch to EXACT if case should matter |
| Matches missing for values you know exist | Check the sheet name in the formula and confirm the key sits in the column being searched | Point the formula at the correct column, or use INDEX with MATCH if the value you need sits left of the key |
| A key that matches the wrong row | Use COUNTIF on the key column and look for any count above 1 | Remove or merge duplicate keys, because the formula returns only the first match |
| Numbers that look right but are not | Compare a few values against the original source | Re-check OCR output or the export, and convert numbers stored as text with VALUE |
📊 At a glance
Three approaches cover the usual lookup tasks in Excel. Each one answers a different question, so compare them against what you need back from the match.
| Approach | What it returns | Use it when | Excel version |
|---|---|---|---|
| ISNUMBER with MATCH | TRUE or FALSE | You only need to know whether a value exists in the other list | Any version |
| XLOOKUP | The matched value, or a label you set | You need a related value, such as a price or a name | Recent versions only |
| INDEX with MATCH | The matched value, or a label wrapped in IFERROR | You need a related value and your version lacks XLOOKUP | Any version |
| EXACT | TRUE or FALSE for identical case and text | Letter case changes the meaning of a match | Any version |
❓ FAQ
1. Does XLOOKUP work in every version of Excel?
No. XLOOKUP is available only in recent versions of Excel. If your version does not recognize it, use INDEX with MATCH, which behaves the same way for exact matches and works in older versions.
2. How do I match on two columns at once, such as name and date?
Create a helper column that joins the two keys, for example =A2&"|"&B2, in both sheets. Then run the normal lookup against the helper column. The separator keeps "AB" plus "C" from matching "A" plus "BC".
3. Why do my numbers fail to match when they look identical?
One side is probably stored as text. Left alignment is a common clue. Convert the text side with VALUE, or multiply by 1, before running the lookup.
4. Can a formula tell me how many times a value appears in the other list?
Yes. Use =COUNTIF(A:A,B2). A result of 0 means no match, 1 means one match, and anything higher means the key is duplicated and needs a check.
📝 Summary
For a yes-or-no match between two columns, use MATCH with 0 inside ISNUMBER. For a value from another sheet, use XLOOKUP or INDEX with MATCH, and add a "Not found" label. Clean the keys and spot-check a few rows before you rely on the results.
