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

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

Hi Miguel,

Thanks for your reply. This seems to be just showing '4' for me in the
first cell.

The end result should hopefully be something like this, say from E1:E3

Pete
Dave
Joe

because they are the names from column A that relate to 'Oranges' in
column B.

Thanks very much,
Dan.


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

That 4 is used by the INDEX function that is also on the post, as I said you
need two columns to use this approach.
If your data layout doesn't allow this, then maybe this is not the solution.

Miguel.

"Voodoodan" wrote:


Hi Miguel,

Thanks for your reply. This seems to be just showing '4' for me in the
first cell.

The end result should hopefully be something like this, say from E1:E3

Pete
Dave
Joe

because they are the names from column A that relate to 'Oranges' in
column B.

Thanks very much,
Dan.


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

Miguel,

One Lucozade and Mars bar later and my brain has kicked in and
understood your instructions correctly!

I have gotten it to work now, couldn't have done that without your
help.

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

Thanks for your feedback, I am happy to help. As this approach is not a
single formula, it is a little harder to explain; I try to make the
explanation as simple as possible, but that is an art by itself.

Miguel.

"Voodoodan" wrote:


Miguel,

One Lucozade and Mars bar later and my brain has kicked in and
understood your instructions correctly!

I have gotten it to work now, couldn't have done that without your
help.

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