Formula for finding text

S

Simon

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
 
P

Paul

Hi,

Use vlookup() but before you do sort the list into ascending order based on
Col A.

Regards
Paul
 
T

TroyW

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
 
G

Guest

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
 
T

TroyW

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
 
Top