Problem Finding Data in Multiple Columns Using Match Function

**joshc22** · 12-12-2012, 04:04 PM

I have a 30000+ row spreadsheet and second version of that spreadsheet which differs by a number of rows. I am trying to use the match function to find out if a row in the new version of the spreadsheet exists in the old version and where.

My formula is as follows
=MATCH(1,(K2=$A:$A)*(L2=$B:$B)*(M2=$C:$C)*(N2=$D:$D)*(O2=$E:$E),0)

It is however displaying #N/A. If I highlight the "MATCH(1,(K2=$A:$A)*(L2=$B:$B)*(M2=$C:$C)*(N2=$D:$D)*(O2=$E:$E),0)" part in the entering field and hit F9 it is acting properly and retuning the correct row location. I am wondering how I can get it to show the correct number without manually hitting F9 for every row. Thanks so much.

**ChemistB** · 12-12-2012, 04:14 PM

Excel isn't storing in memory each of the results as it goes through and checks K2 against A1, then A2, etc. You could fix this by changing this to an Array function and using CNTRL SHFT ENTER instead of ENTER (you'll see {} around the formula). HOWEVER, with that many rows, it might lock up your sheet. You could also try this non-array entered formula

=SUMPRODUCT((K2=$A:$A)*(L2=$B:$B)*(M2=$C:$C)*(N2=$D:$D)*(O2=$E:$E)*ROW(A:A))

Also, restricting the contents of the columns to where the data is. (i.e. $A$2:$A$30,004). Then you're not wasting memory on empty rows.
Any of this work for you?

**joshc22** · 12-12-2012, 04:26 PM

I plugged in your version and its working completely correctly! Dropped it down to all the rows and looks like it might take an hour or so but its slowly chugging away. Thanks so much!

Problem Finding Data in Multiple Columns Using Match Function

LinkBack

Thread Tools

Rate This Thread

Display

Problem Finding Data in Multiple Columns Using Match Function

Re: Problem Finding Data in Multiple Columns Using Match Function

Re: Problem Finding Data in Multiple Columns Using Match Function

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions