If you can use an additional column, this may work for you. In cell E2 enter:
=E1 +MATCH("Oranges",INDIRECT("B" & 1 + E1 & ":B9"),0)
E1 should be blank or 0. If your lookup range is bigger than the one shown,
change the ":B9" part in the formula to match your range. You can also
change the "Oranges" part to a cell reference.
With this, you can use this formula in D2:
=INDEX($A$1:$A$9,E2)
Again, change the range to match your lookup table.

Hope this helps,
Miguel.

"Voodoodan" wrote:


Hi there,

I've been looking at Lookup, Match, Index, Find, etc., to do this, but
I am getting nowhere with it!!

I have a list, like this example:

Dan Apples
Fred Apples
John Apples
Pete Oranges
Dave Oranges
Joe Oranges
Albert Bananas
Kev Bananas
Doris Bananas

What I need, say in cell D2, is a formula to find the first appearance
or oranges, then return the first name, ie, "Pete". Then in D3 it
would do the same but one orange down, to show the name "Dave", etc.

Can anyone advise me further please, I'm not quite sure how to do
this?!?

Many thanks,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=545483