Lookup and list

[Voodoodan]

Hi there,

I've been looking at Lookup, Match, Index, Find, etc., to do this, bu
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 appearanc
or oranges, then return the first name, ie, "Pete". Then in D3 i
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 d
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]

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]

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.