Simple Match function (with a twist)

[harryco79]

Hi guys,
Looking to perform a match within just one column (B1:B1400). I am basically trying to see if the content in a row matches any in the column above. But i want to exclude the row i have the formula in from the search.

eg. in cell c1 i have the formula "=MATCH (A1, B1:B1400, 0)" , which takes the value in A1 and returns the row number it finds a match in....except i want the to exclude ROW 1 from that search.

Does this make sense? Seems simple enough but unable to figure it out. Pls help!

Cheers

 

[Jonmo1]

Try

=MATCH(A1,"B2:B1400,0)+1

 

[harryco79]

wow super fast response. Thanks a lot. Was just ready my post and realized i had made a bit of an error...couldnt edit quick enough...my bad! ;-(

Really what I am trying to do is perform a match within just one column (B1:B1400). I am basically trying to see if the content in a specific row matches any in the column above. But i want to exclude the row i have the formula in from the search.

eg. in cell c1 i have the formula "=MATCH (B1, B1:B1400, 0)" , which takes the value in B1 and returns the row number it finds a match in....except i want the to exclude ROW 1 from that search.


so sorry for the confusion

 

[Jonmo1]

Is your ultimate goal to mark and then remove duplicates??

 

[harryco79]

My goal is really to just know the row number a duplicate exists in..which i think the match function is suited for....

so if there is a duplicate of B1 in B255, I want the value "255" to show in my result but right now it shows "1" as the result because it counts row 1 as part of the search

Thanks

 

[Jonmo1]

Will there be only 1 or 2 matches for B1 (B1 and/or B255)?
Or might there be 3 or more (B1, B255, B326, etc..)
If there are more, which one do you want?

 

[harryco79]

there will be only 1 match in row 255 (as there would be only 1 other possible duplicate..if any at all)

 

[Jonmo1]

Try

=LOOKUP(2,1/(B$1:B$1400=B1),ROW(B$1:B$1400))

 

[harryco79]

seems to work great except it returns the value "1400" (which is the last row number) in cells that have no data in them....is there a way to exclude that unwanted result?

Thanks a MILLION!

 

[Jonmo1]

seems to work great except it returns the value "1400" (which is the last row number) in cells that have no data in them....is there a way to exclude that unwanted result?

Thanks a MILLION!

That would only happen if B1 is also blank...

Try
=IF(B1="","",LOOKUP(2,1/(B$1:B$1400=B1),ROW(B$1:B$1400)))