finding a match
 

I've got a spreadsheet that is getting new entries posted to it. I want to
know if a the same person with the same dollar amount has already been
entered before. The problem is that the MATCH formula is matching the line
with itself if it doesn't find a prior match. Is there any way to have the
match stop at the row just prior to the one being entered? (Without the user
having to maintain the formula)
Ex:

{=MATCH(A4&B4,$A:$A&$B:$B,0)}

NAME $$ Matched Row
joe 20 1 {=MATCH(A1&B1,$A:$A&$B:$B,0)}
sam 15 2 {=MATCH(A2&B2,$A:$A&$B:$B,0)}
ralph 50 3 {=MATCH(A3&B3,$A:$A&$B:$B,0)}
joe 20 1 {=MATCH(A4&B4,$A:$A&$B:$B,0)}

finding a match
 

Hi,

You need a header row for this to work so with your first row of data in
A2&B2 put this in c2

=IF(ISERROR(MATCH(A2&B2,$A$1:$A1&$B$1:$B1,0)),"",M ATCH(A2&B2,$A$1:$A1&$B$1:$B1,0))


Enter as an array (CTRL+Shift+Enter) and drag down.

Mike
"SandyL" wrote:

I've got a spreadsheet that is getting new entries posted to it. I want to
know if a the same person with the same dollar amount has already been
entered before. The problem is that the MATCH formula is matching the line
with itself if it doesn't find a prior match. Is there any way to have the
match stop at the row just prior to the one being entered? (Without the user
having to maintain the formula)
Ex:

{=MATCH(A4&B4,$A:$A&$B:$B,0)}

NAME $$ Matched Row
joe 20 1 {=MATCH(A1&B1,$A:$A&$B:$B,0)}
sam 15 2 {=MATCH(A2&B2,$A:$A&$B:$B,0)}
ralph 50 3 {=MATCH(A3&B3,$A:$A&$B:$B,0)}
joe 20 1 {=MATCH(A4&B4,$A:$A&$B:$B,0)}

finding a match
 

That worked great! Now, is there a way to highlight the row if there is a
match?

"Mike H" wrote:

Hi,

You need a header row for this to work so with your first row of data in
A2&B2 put this in c2

=IF(ISERROR(MATCH(A2&B2,$A$1:$A1&$B$1:$B1,0)),"",M ATCH(A2&B2,$A$1:$A1&$B$1:$B1,0))


Enter as an array (CTRL+Shift+Enter) and drag down.

Mike
"SandyL" wrote:

I've got a spreadsheet that is getting new entries posted to it. I want to
know if a the same person with the same dollar amount has already been
entered before. The problem is that the MATCH formula is matching the line
with itself if it doesn't find a prior match. Is there any way to have the
match stop at the row just prior to the one being entered? (Without the user
having to maintain the formula)
Ex:

{=MATCH(A4&B4,$A:$A&$B:$B,0)}

NAME $$ Matched Row
joe 20 1 {=MATCH(A1&B1,$A:$A&$B:$B,0)}
sam 15 2 {=MATCH(A2&B2,$A:$A&$B:$B,0)}
ralph 50 3 {=MATCH(A3&B3,$A:$A&$B:$B,0)}
joe 20 1 {=MATCH(A4&B4,$A:$A&$B:$B,0)}