Lookup and list

  • Thread starter Thread starter Voodoodan
  • Start date Start date
V

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
 
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.
 
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.
 
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.
 
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.
 
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.
 
Back
Top