Need some help with the MATCH formula in Excel. I have several rows of
numbers, one cell in each row will be constant (always the same
number). I have another row in which the numbers always change. In one
cell of this row (J36), this cell is used in the match formula from one
cell of the rows in which the numbers remain constant.

So...I have cell J36, which changes frequently, I have cells J38, J39,
J40, J41 etc...etc... that remain constant. If cells J38:J41 match the
value in J36, I get a return of "1", if they don't match, I get
"#N/A".
My formula resides in cells Q38 - Q41.

Is there another formula I can use instead of MATCH, that if the values
match, I get text ("MATCH"), if I don't get a match - the cells remains
blank?

Thanx in advance.


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=515052

You can test the result of the match function with the isna function.
Instead of =match(...), try =if(isna(match(...)),"","MATCH")
--Bruce

"Ltat42a" wrote:


Need some help with the MATCH formula in Excel. I have several rows of
numbers, one cell in each row will be constant (always the same
number). I have another row in which the numbers always change. In one
cell of this row (J36), this cell is used in the match formula from one
cell of the rows in which the numbers remain constant.

So...I have cell J36, which changes frequently, I have cells J38, J39,
J40, J41 etc...etc... that remain constant. If cells J38:J41 match the
value in J36, I get a return of "1", if they don't match, I get
"#N/A".
My formula resides in cells Q38 - Q41.

Is there another formula I can use instead of MATCH, that if the values
match, I get text ("MATCH"), if I don't get a match - the cells remains
blank?

Thanx in advance.


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=515052

bpeltzer Wrote:
You can test the result of the match function with the isna function.
Instead of =match(...), try =if(isna(match(...)),"","MATCH")
--Bruce



Thanx Bruce, I tired that, my formula -
=IF(ISNA(MATCH(J36,J38)),"","MATCH")
When I put in the same number in J36 as J38, I do get the "Match"
results, however, if I put in a different number in J36, the result
still says "Match" no matter what number I put in J36.

J36 is the cell that changes frequently; J38 remains constant.


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=515052

It looks like the arguments of your match function are wrong. Generally, the
second argument (where you have J38) would be a range of cells where the
match function should look; otherwise, you could just use IF (as in if
j36=j38). Next, if you want an exact match, you should provide the match
function with a third argument, FALSE.
If the function originally was returning NA and you dropped that same
function inside the isna test, the result should be blank.
--Bruce

"Ltat42a" wrote:


bpeltzer Wrote:
You can test the result of the match function with the isna function.
Instead of =match(...), try =if(isna(match(...)),"","MATCH")
--Bruce



Thanx Bruce, I tired that, my formula -
=IF(ISNA(MATCH(J36,J38)),"","MATCH")
When I put in the same number in J36 as J38, I do get the "Match"
results, however, if I put in a different number in J36, the result
still says "Match" no matter what number I put in J36.

J36 is the cell that changes frequently; J38 remains constant.


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=515052