Exclude current row from VLOOKUP?

Question

When using VLookup is there a way to exclude the current row?

I'm trying to determine the following:

If two rows have the same value in column A,  
check if they have the same value in column B.

It seems to me that

=exact(B2,vlookup(A2,A:C,2,FALSE))

should be able to do that, the only thing I can't figure out is how to ignore the current row so it doesn't compare itself to itself.

Just starting the vlookup range one row lower than the current row would work, except it's possible that the row with the matching value in column A is either above or below the current row.

Thanks!

user_4851590 · Answer

Use COUNTIFS instead:

=COUNTIFS(A:A,A2,B:B,B2)>1

user_5632629 · Answer

If two rows have the same value in column A,
check if they have the same value in column B.

use:

=ARRAYFORMULA(IF(A2:A&B2:B="",,COUNTIFS(
 FLATTEN(QUERY(TRANSPOSE(IFERROR(CODE(REGEXEXTRACT(A2:A&B2:B, 
 REPT("(.)", LEN(A2:A&B2:B)))))),,9^9)), 
 FLATTEN(QUERY(TRANSPOSE(IFERROR(CODE(REGEXEXTRACT(A2:A&B2:B, 
 REPT("(.)", LEN(A2:A&B2:B)))))),,9^9)))>1))

user_15153326 · Answer

You can use the range you want to check for the vlookup

So you can use this query: =exact(A1,vlookup(A1,A1:B4,2,FALSE))

Note: A1:B4 is the range for the vlookup.

For more about vlookup you can check: Link.

Exclude current row from VLOOKUP?

3 Answers