Problem searching cell text
 

Hi,

I posted a prior question about searching text within cells and entering
particular values in another cell if found, or else enter "other" in the
destination cell. I received this formula in response:

=IF(SUM(-ISNUMBER(SEARCH(List,A1)))=0,"Other",
INDEX(List,MATCH(TRUE,ISNUMBER(SEARCH(List,A1)),0) ))

This returns "Other" even when text in the search cell matches text in the
List however. I have included an exact example below. Can anyone tell me
what I am doing wrong?

Search Cells:
A
1 Back Testing
Q2 2008
2 CAT
Q3 2008
3 CAT
4 CAT
Database
XYZ Server
Q3 2008
5 Database
Q2 2008
ROA & SAP

List
Auto Release
Back Testing
Future Support
HW Upgrades
Null & 0 Value Limits
Other Apps
POV Renovation
ROA & SAP
VWAP Rule
XYZ GUI
XYZ Server

My desired result is for the formula to return "Back Testing" when searching
A1, "Other" for A2 & A3, "XYZ Server" for A4, and "ROA & SAP" for A5.

Instead, "Other" is returned each time.

Thank you!

Tom

Problem searching cell text
 

Tom,

It works fine for me. Are you entering the formula with Ctrl-Shift-Enter? Is List a properly named
ranges (a ingle column) of values? Are there extra spaces (that you don't see) at the end of your
list values?

HTH,
Bernie
MS Excel MVP


"tommcbrny" wrote in message
...
Hi,

I posted a prior question about searching text within cells and entering
particular values in another cell if found, or else enter "other" in the
destination cell. I received this formula in response:

=IF(SUM(-ISNUMBER(SEARCH(List,A1)))=0,"Other",
INDEX(List,MATCH(TRUE,ISNUMBER(SEARCH(List,A1)),0) ))

This returns "Other" even when text in the search cell matches text in the
List however. I have included an exact example below. Can anyone tell me
what I am doing wrong?

Search Cells:
A
1 Back Testing
Q2 2008
2 CAT
Q3 2008
3 CAT
4 CAT
Database
XYZ Server
Q3 2008
5 Database
Q2 2008
ROA & SAP

List
Auto Release
Back Testing
Future Support
HW Upgrades
Null & 0 Value Limits
Other Apps
POV Renovation
ROA & SAP
VWAP Rule
XYZ GUI
XYZ Server

My desired result is for the formula to return "Back Testing" when searching
A1, "Other" for A2 & A3, "XYZ Server" for A4, and "ROA & SAP" for A5.

Instead, "Other" is returned each time.

Thank you!

Tom

Problem searching cell text
 

Bernie, thanks for the help. There were in fact spaces after almost every
entry in the list, I didn't realize they were there and also didn't know they
would affect the performance. Thank you!

Tom

"Bernie Deitrick" wrote:

Tom,

It works fine for me. Are you entering the formula with Ctrl-Shift-Enter? Is List a properly named
ranges (a ingle column) of values? Are there extra spaces (that you don't see) at the end of your
list values?

HTH,
Bernie
MS Excel MVP


"tommcbrny" wrote in message
...
Hi,

I posted a prior question about searching text within cells and entering
particular values in another cell if found, or else enter "other" in the
destination cell. I received this formula in response:

=IF(SUM(-ISNUMBER(SEARCH(List,A1)))=0,"Other",
INDEX(List,MATCH(TRUE,ISNUMBER(SEARCH(List,A1)),0) ))

This returns "Other" even when text in the search cell matches text in the
List however. I have included an exact example below. Can anyone tell me
what I am doing wrong?

Search Cells:
A
1 Back Testing
Q2 2008
2 CAT
Q3 2008
3 CAT
4 CAT
Database
XYZ Server
Q3 2008
5 Database
Q2 2008
ROA & SAP

List
Auto Release
Back Testing
Future Support
HW Upgrades
Null & 0 Value Limits
Other Apps
POV Renovation
ROA & SAP
VWAP Rule
XYZ GUI
XYZ Server

My desired result is for the formula to return "Back Testing" when searching
A1, "Other" for A2 & A3, "XYZ Server" for A4, and "ROA & SAP" for A5.

Instead, "Other" is returned each time.

Thank you!

Tom

Problem searching cell text
 

Tom,

You're welcome, and thanks for letting us know that you figured it out.

Bernie
MS Excel MVP


"tommcbrny" wrote in message
...
Bernie, thanks for the help. There were in fact spaces after almost every
entry in the list, I didn't realize they were there and also didn't know they
would affect the performance. Thank you!

Tom

"Bernie Deitrick" wrote:

Tom,

It works fine for me. Are you entering the formula with Ctrl-Shift-Enter? Is List a properly
named
ranges (a ingle column) of values? Are there extra spaces (that you don't see) at the end of
your
list values?

HTH,
Bernie
MS Excel MVP


"tommcbrny" wrote in message
...
Hi,

I posted a prior question about searching text within cells and entering
particular values in another cell if found, or else enter "other" in the
destination cell. I received this formula in response:

=IF(SUM(-ISNUMBER(SEARCH(List,A1)))=0,"Other",
INDEX(List,MATCH(TRUE,ISNUMBER(SEARCH(List,A1)),0) ))

This returns "Other" even when text in the search cell matches text in the
List however. I have included an exact example below. Can anyone tell me
what I am doing wrong?

Search Cells:
A
1 Back Testing
Q2 2008
2 CAT
Q3 2008
3 CAT
4 CAT
Database
XYZ Server
Q3 2008
5 Database
Q2 2008
ROA & SAP

List
Auto Release
Back Testing
Future Support
HW Upgrades
Null & 0 Value Limits
Other Apps
POV Renovation
ROA & SAP
VWAP Rule
XYZ GUI
XYZ Server

My desired result is for the formula to return "Back Testing" when searching
A1, "Other" for A2 & A3, "XYZ Server" for A4, and "ROA & SAP" for A5.

Instead, "Other" is returned each time.

Thank you!

Tom