Google Spreadsheets, match/lookup in ALL rows, except the current one

Question

Google Spreadsheets, match/lookup in ALL rows, except the current one

I have never come across a method that looks in all rows, except the current.

Say you have a match or vlookup and it looks in ALL the rows except the current one the formula is in.

We use a formula like below that verifies if a certain value already exists (using a MAX), but if it finds itself then the match or vlookup is always 1 (or in error)

A   B
1   Formula: Does value 1 from cell A1 exist in column A? 
2   Formula: Does value 1 from cell A2 exist in column A?
3   Formula: Does value 1 from cell A3 exist in column A?
4   Formula: Does value 1 from cell A4 exist in column A? Check all except row 4

Something like this

Formula in cell C4: match(A4;A$1:A3&A5:A)
Or Formula in cell C4: match(A4;A:A&[^A4])

Answer 1

You can use this formula from the second row of data onward (B2=):

=IFERROR(MATCH(A2,A$1:A1,0),MATCH(A2,A3:A,0)+ROWS(A$1:A1)+1)

If there is no match above the current cell it will search for a match below, and will return the correct index in the full column range by adding ROWS(A$1:A1)+1 to the below match result.

Copy this formula downward from B2 to B3 and so on.

Answer 2

If you are trying to check whether the value appears in the column except for the current row just use COUNTIF()

Put this formula in B2

=ARRAYFORMULA(IF($A$2:$A="",,IF(COUNTIF($A2:$A,"="&$A2:A)>1,True,False)))

Otherwise please specify what kind of output you want