Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for finding text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for finding text
Hi,
Use vlookup() but before you do sort the list into ascending order based on Col A. Regards Paul "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for finding text
H
Try left(A1,2 HT Steven |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for finding text
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for finding text
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for finding text
Sorry misread question. Its still early :-\
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for finding text
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with formula: finding text | Excel Worksheet Functions | |||
Finding text | Excel Worksheet Functions | |||
Finding text | Excel Worksheet Functions | |||
Finding text in a cell and returning a value based on that text | Excel Discussion (Misc queries) | |||
Finding Specific Text in a Text String | Excel Worksheet Functions |