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:


I currently use the following formula:


Which has the output


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


You can use this formula in D2


confirmed with CTRL+SHIFT+ENTER and copied down

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

  • +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:


Of course, a filter could make your life easier.

  • 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:


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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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