# Excel: Match Twice in Same Column

What is an Excel formula to search a column for a match, and then beginning at that row, find a another match within the same column. For example, imagine a column containing 1000's of letters.

A B C B D ...

I want to find the index, ideally absolute, of B that follows C.

-
If letters are in, say, cells A1 to A1000, then use formula =MATCH("C",OFFSET(A1:A1000,MATCH("C",A1:A1000,0),0),0)+MATCH("C",A1:A1000,0). From AllExperts.Com. – chuff Jan 19 '13 at 7:00
=MATCH("B",OFFSET(A1:A1000000,MATCH("C",A:A,0),0),0)+MATCH("C",A:A,0) ? If so, please repost as an answer so you can receive well deserved credit. Is there a way to specify bottom instead of A1000000? – BSalita Jan 19 '13 at 9:53

chuff's answer works for me and might well be the best approach but you could also use this "array" version that includes the whole column

`=MATCH("B",IF(ROW(A:A)>MATCH("C",A:A,0),A:A),0)`

confirmed with CTRL+SHIFT+ENTER

works in Excel 2007 or later

-
Wouldn't this run into trouble if there were no "C" in the column? – chuff Jan 19 '13 at 14:12
@chuff I think Bsalita wants to find the first "B" that comes after a "C" - don't know what should happen if there's no "C" - my version will give #N/A if there's no "C"....or no "B" after that "C" – barry houdini Jan 19 '13 at 15:08
The spreadsheet is computer generated and guaranteed to have C and guaranteed to have B following C. – BSalita Jan 19 '13 at 19:48
Will the array formula work in LibreOffice? The spreadsheet needs to be cross-platform. – BSalita Jan 19 '13 at 19:49
I don't usually use LibreOffice but when I tried the formula works but not with the whole column so it would be back to `=MATCH("B",IF(ROW(A1:A1000)>MATCH("C",A1:A1000,0),A1:A1000),0)` or similar – barry houdini Jan 19 '13 at 21:35

If the column of letters is in, say, cells A1 to A1000, then to find "B" use formula:

``````=MATCH("B",OFFSET(A1:A1000,MATCH("B",A1:A1000,0),0),0)+MATCH("B",A1:A1000,0)
``````

Will get back to you on how to change to remove hard reference to 1,000th row.

-

Select the column which has the entries that shouldn't be identified twice. Then under Data tab click on the filter icon under Advanced. Select `unique entries only`. Any entries entered more than once will not be shown!

-