If I understand correctly, the list is on sheet "sales" and the value to
match is in cell C9 on sheet "fruit". The following formula can be placed in
any cell on any sheet:
=INDEX(sales!$B$1:$B$4,MATCH(fruit!C9,sales!$A$1:$ A$4,0),1)

Troy

wrote in message
...
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



.