Concise way to match last value against any other value in range

[TheN]

So I am looking for a concise way to see if the last cell in a row within a specified range matches any previously entered value within that row.

For example, looking at this data:

1	23	34	1		match
2	3	3			match
3	5	4	6	5	match

Now it would be looking at a much larger dataset, and I would prefer if I didn't need to test each individually (there can be a lot of tests).

I can use:

Please Login or Register  to view this content.

to return the last non-zero value, but I am stuck on the concise way to test against all previous values. I can only think of volatile ways utilizing indirect, which I want to avoid if at all possible mainly due to the sheer size of my file.

[TMS]

Why can't you just use COUNTIF?

[FDibbins]

Just how big is this sheet?

[kvsrinivasamurthy]

Data in D2:K4, formula in L2,then drag down

Please Login or Register  to view this content.

[FlameRetired]

Another way.

Please Login or Register  to view this content.

	A	B	C	D	E	F
1	1	23	34	1		match
2	2	3	3			match
3	3	7	4	6	9	not

[Tony Valko]

Here's another one...

Data Range

	A	B	C	D	E	F
1	------	------	------	------	------	------
2	1	23	34	1		Match
3	2	3	3			Match
4	3	5	4	6	5	Match

This formula entered in F2 and copied down:

=IF(COUNTIF(A2:E2,LOOKUP(1E100,A2:E2))>1,"Match","")

[TheN]

Why can't you just use COUNTIF?

I posted it when it was quite late, and simply overlooked the obvious. Thanks everyone for your time.

I especially like Tony Valko's solution, so I am glad I posted anyway, it looks like a very efficient way to do it.

Once again, thank you everyone.

My final formula ended up being quite complicated, but the format provided cleaned it up to the point where you can actually see what's going on.

Please Login or Register  to view this content.

[Tony Valko]

You're welcome. Thanks for the feedback!

[TMS]

@TheN: yep, that's what I wondered. I'd probably have done something similar to Tony's solution with your original LOOKUP. But away for the weekend and only got iPad with me. Glad you have learnt from it.

[TMS]

Thanks for the rep.