•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to compare two full columns without #SPILL error in new Excel?
Our take
Are you frustrated with Excel's #SPILL! error when trying to compare two full columns? In older versions, a straightforward formula like =A:A=B:B worked seamlessly, allowing you to identify matches and differences effortlessly. However, with the introduction of dynamic arrays in newer Excel versions, this method no longer functions as expected. Fortunately, there’s a simple solution that doesn’t involve complex formulas, VBA, or conditional formatting. Keep reading to discover an easy way to compare columns A and B without the hassle of errors.
I need to compare two columns (A:A and B:B) row-by-row to identify matches and differences.
In older Excel versions, I used =A:A=B:B which worked perfectly - it compared entire columns and auto-adjusted when I inserted/moved rows.
In newer Excel (with dynamic arrays), this formula returns #SPILL! error.
I know there's a solution (I used one successfully in mid-2025 but lost the formula. I've asked two diff AI Models but they cant solve it. I'm looking for something SIMPLE not conditional formatting or VBA or long ass formulas that I dont have any idea what's happening.
Can anyone help?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Is it possible to give a formula to mark the differences between Columns A & B?https://preview.redd.it/7gapmw6k6trg1.jpg?width=253&format=pjpg&auto=webp&s=fc30350d48dd5888c4dacf76b39fc23b98e9bad8 Please suggest a formula for Google Sheets to find the missing (or extra) figure in B. Would the formula differ for Excel? submitted by /u/ecomfreelancer [link] [comments]
- Comparing columns and extranting a valueI need help (I’m very new to Excel). I need a formula to help me with this: I have a large list of items in column A and corresponding values in column B. Now, in column C, I have a short list that is included in column A. I need a formula to compare columns A and C, and if the values match, then write in column D the corresponding value from column B. submitted by /u/Royal_Instance4497 [link] [comments]
- How to prevent !SPILL error?Hi all, I'm making a spreadsheet and I keep getting a SPILL error in one of my boxes. I'm taking some of my data from one tab on another excel to calculate a % change but each time I do it, the tab spills over one extra cell for no apparent reason? Annoyingly it is just returning the same value also so there's no reason I can see for it to spill? As I move through the months the amount of cells it spills into increases. It's infuriating, can anybody help at all? Thanks https://preview.redd.it/rplx7e6i79vg1.png?width=1657&format=png&auto=webp&s=eea0d48c6edecb7a8699aa4e057ed47fc0bbca99 submitted by /u/Ok-Let-5155 [link] [comments]