Forum Discussion

emmaauger's avatar
emmaauger
Copper Contributor
Jan 03, 2024

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 =IF(ISNUMBER(MATCH(B:B, A:A, 0)), "Match", "No Match") but it gave me the #SPILL error. 

 

How could i see if there's duplicates/matchs in both columns in one formula?

 

Formulas and Functions

1 Reply

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 03, 2024

    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")
)

     

     

Resources