Thread: Match function for non-unique entries

Hi!

I have say two columns of data.

Name Count
A 1
B 2
C 2
D 3
E 2
F 6
A 3
G 5
B 2
C 1

Now I need to organize the data. N for that I need the column numbers where a particular data is ocurring in the list in the Name column. A match function for say "A" will give the first instance of occurrence. Is there a formula which can incorporate an array and give me a list of all the column numbers where "A" occurs. Preferable automated in a way where the column numbers appear in different cells.

If your sample data occupies A1:B11 with the headings in A1:B1 and if the column A value that you are wanting to track is in C1 then one way is this array formula that is entered into C2 using Ctrl+Shift+Enter key combination then filled down to C11

IF(ROW($A1)>COUNTIF($A$2:$A$11,$C$1),"",SMALL(IF($A$2:$A$11=$C$1,ROW($A$2:$A$11),""),ROW($A1)))

But this does not work for duplicate entries. It returns the row number of the first occurence.

Originally Posted by SunayanaG

But this does not work for duplicate entries. It returns the row number of the first occurence.

Perhaps you have not array entered the fomula.
"A" in column A is in rows 2 and 8 and those values are returned by the formulas in C2 and C3 while C4:C11's formulas all return blanks.
See attachment