Return part of a cell value within a range

[PeteG]

Hi,
I was hoping that I could come up with a formula to solve this but no luck so far.

I have a list of names and I want to compare each one to a list of 'part names' and return the 'part name' that matches, if there is one.

I have this:

=IF(ISNUMBER(FIND(Sheet1!A22,A2,1)),Sheet1!A22,"can't find")

which will return the cell content of Sheet1!A22 if it exists within A2 but I want to do that check across a range.

What I want to achieve is akin to having the above as an array formula, like:

{=IF(ISNUMBER(FIND(Sheet1!A1:A22,A2,1)),Sheet1!A1:A22,"")}

but that doesn't work, of course. Also, I haven't considered what would happen if there was more than one match - but in that case it would be fine to return the 'first' match only.

Any advice would be welcome!

Thanks

 

[SkipVought]

Hi,

Finding part of a value in a range is a problem in Excel unless you use VBA (macro) code.

But before we go off on that tangent, please post an example of a typical value you might be searching for and the values in the range that would contain that value. Please include the "more than one match" scenario as well.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PeteG]

OK, I might have a list of customer names like:

Redhill Health
Manchester University Campus
Oxford Health
Liverpool Health Service

and a list of part-names like:
Service
Health
University

So, the formula would return "Health" for "Redhill Health" and "University" for "Manchester University Campus" because it would find those part-names within the customer name.
"Liverpool Health Service" obviously contains both "Health" and "Service"; it would not matter which part-name it returned, as long as it did return one of them.

 

[SkipVought]

I hope that your Customer List has a Heading and ALL the rows in your list are contiguous.

Select your Customer List and turn on the AutoFilter.

Select the AutoFilter BUTTON and select CUSTOM

Show rows where Customer List

Select CONTAINS from the Drow Down

Enter Health in the adjacent criteria box. OK

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PeteG]

Hi,
Fair enough, that would give me a filtered list but I'd need to change the criteria each time, rather than just having the 'part-name' listed next to the name, which is what I was hoping to achieve.

I should add that I have just solved this outside of Excel, by adding a case statement into my original sql query that generated the cusomer list. However, I'd be interested to know if there is a technique that would do it (although I accept that VBA would be a simpler alternative in the long run)

So, my immediate need is over - but thanks for taking the time to reply.

 

[SkipVought]

rather than just having the 'part-name' listed next to the name

What did you want to happen when there are multiples?

I'd suggest putting a Data > Validation - LIST that contains your lookup values, ABOVE the Customer List.

Turn on your macro recorder and record selecting a criterial as outlined above.

Use the Worksheet_Change event in the Sheet Code Window, to trigger the macor that changes the AF. Modify the AF to accept the select value in the Data > Validation - LIST

Post in Forum707, if you need help with this approch.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}