Lookup and list

[Voodoodan]

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, with names in column A and items in column B, 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 of 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.

[Miguel Zapico]

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

[Voodoodan]

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.

[Miguel Zapico]

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

[Voodoodan]

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.

[Miguel Zapico]

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