Problem searching cell text

[tommcbrny]

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

 

[Bernie Deitrick]

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]

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]

Tom,

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

Bernie
MS Excel MVP