Forum Discussion
match column B to column A
Hi! I'm trying to match cells from column B to Column A. The thing is i have thousands of cells so i need a formula that can do entire columns in one go. It looks like this. i tried using...
The #SPILL! error is the result of starting the dynamic array formula in row 2. Essentially, Excel is saying you need 1 more row to deposit the results because you're matching the entire column.
You can fix this by making the reference to B dynamic.
=LET(
rng, B2:B10000,
nonblank, COUNTA(rng),
lookup_values, TAKE(rng, nonblank),
IF(ISNUMBER(XMATCH(lookup_values, A:A)), "Match", "No Match")
)