Thats great it works a treat. One last thing if the
orginal list is on a tab called sales and the answer is on
a different worksheet called fruit how do I need to amend
the second search you provided?

Thanks in advance
-----Original Message-----
Simon,

Give these a try:
=INDEX($B$1:$B$4,MATCH(A3,$A$1:$A$4,0),1)
or
=INDEX($B$1:$B$4,MATCH("Apple",$A$1:$A$4,0),1)

If the value isn't found it returns #N/A

Troy

"Simon" wrote in
message
...
What formula should I use to lookup a piece of text
from a
list of entries in for example colomn a I want to lookup
apple and if found then return the data from colomn b
i.e.
AP into cell 5a.

A B
1 Potato PO
2 Carrot CA
3 Apple AP
4 Swede SW

5 Formula result

It sounds simple but I can only get matches to work with
numbers and not text. Any ideas?

Thanks



.