Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a sheet with the following data:

  A         B
1 Raisa     A
2 Mellissa  A
3 Emma      A
4 Slyvia    B
5 Salina    C
6 Beula     A
7 Apolonia  F

And I would like to list all people with A's, like this:

Raisa
Mellissa
Emma
Beula

I currently use the following formula:

=INDEX(A1:A7;MATCH("A";B1:B7;0))

Which has the output

Raisa

How do I alter the formula so the following cells each show the 'next match'?

share|improve this question
5  
Filter on ColumnB would seem much easier. –  pnuts Mar 14 '13 at 19:20
    
Similar question on stackoverflow: stackoverflow.com/questions/12096573/… –  Brad Patton Mar 14 '13 at 19:34

3 Answers 3

You can use this formula in D2

=IFERROR(INDEX(A$1:A$7;SMALL(IF(B$1:B$7="A";ROW(B$1:B$7)-ROW(B$1)+1);ROWS(D$2:D2)));"")

confirmed with CTRL+SHIFT+ENTER and copied down

I used ; as separators because that's the style used in the question

share|improve this answer
    
+1, the route I chose not to take. –  nutsch Mar 14 '13 at 22:14

If there are no duplicates in your data, keep on using your formula in the first cell (I'll assume E1, then use the following to copy down:

=INDEX(offset($A$1:$A$7;MATCH(E1;$A$1:$A$7;0);0;;);MATCH("A";offset($A$1:$A$7;MATCH(E1;$A$1:$A$7;0);1;;);0))

Of course, a filter could make your life easier.

share|improve this answer
    
Your formula has multiple issues: 1) it should most likely be MATCH(E1;$B$1:$B$7 not ...$A.... 2) replace ; with , (international standard) 3) The formula will always return the second item. You probably omitted something! –  Peter Albert Mar 14 '13 at 19:29
    
nutsch's formula work's OK for me, Peter - once valid returns run out you get #N/A but that can be eliminated with ISERROR as per my answer.....or yours –  barry houdini Mar 14 '13 at 20:08
1  
@PeterAlbert: 1)it should only be $B if I didn't offset the column, 2)the question is working on French or other regional settings with semi-columns, so I stuck with that presentation. –  nutsch Mar 14 '13 at 22:13
    
My bad! I oversaw the parameter 1 in the OFFSET. +1 –  Peter Albert Mar 15 '13 at 7:16

Similar to nutsch's answer, try this formula in C2:

=IFERROR(INDEX(OFFSET($A$1:$A$7,MATCH(C1,$A$1:$A$7,0),0),MATCH($E$1,OFFSET($B$1:$B$7,MATCH(C1,$A$1:$A$7,0),0),0)),"")

This assumes that you use your formula =INDEX($A$1:$A$7,MATCH($E$1,B1:B7,0)) in C1 - and store the value to look for (i.e. A) in cell E1.

(Note: replace the , with ; according to your local settings)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.