Formula to compare cell value with another column

[rajesh2022]

I am trying to compare cells with another column. I need partial match, I tried vlookup but it gives exact match only.

Here is scenrio, in cell I have value as "Commercial and Operation Manager", this value I need to compare with another column range, where I have cell value Operation (Partial Match), if match found, need to pull next column value.

So I will not get exact match always, some times partial match.

Attaching sample file for reference.

 

[bosco_yip]

Try,

In Sheet1 cell B1, formula copied down:

=LOOKUP(1,-SEARCH(Sheet2!A$2:A$30,Sheet1!A1),Sheet2!B$2:B$30)

 

[rajesh2022]

Try,

In Sheet1 cell B1, formula copied down:

=LOOKUP(1,-SEARCH(Sheet2!A$2:A$30,Sheet1!A1),Sheet2!B$2:B$30)

View attachment 82428

Sorry, I noticed one issue, now I added few more records to excel and tried, getting errors

Example : If I added new word in Column A as "Director of Marketing" and sheet 2 I have 2 more records as follows:
1. CTO - IT
2. Marketing - Marketing.

Formula will update as "IT" because its considering CTO from Director word. Ideally I need output as "MArketing" which as a word.

 

[bosco_yip]

Sorry, I noticed one issue, now I added few more records to excel and tried, getting errors

Example : If I added new word in Column A as "Director of Marketing" and sheet 2 I have 2 more records as follows:
1. CTO - IT
2. Marketing - Marketing.

Formula will update as "IT" because its considering CTO from Director word. Ideally I need output as "MArketing" which as a word.

To extend the Sheet2 Lookup range and Result range, as in:



So,

In Sheet1 cell B1, revised formula copied down:

=LOOKUP(1,-SEARCH(Sheet2!A$2:A$32,Sheet1!A1),Sheet2!B$2:B$32)

 

[rajesh2022]

To extend the Sheet2 Lookup range and Result range, as in:

View attachment 82432

So,

In Sheet1 cell B1, revised formula copied down:

=LOOKUP(1,-SEARCH(Sheet2!A$2:A$32,Sheet1!A1),Sheet2!B$2:B$32)

Sorry, if I am not clear on my question. In above example for "Director of Marketing " I need output as "Marketing", currently its showing as IT. because formula considering the cto which is in Director

 

[bosco_yip]

Sorry, if I am not clear on my question. In above example for "Director of Marketing " I need output as "Marketing", currently its showing as IT. because formula considering the cto which is in Director

Post out your file to check please.

 

[rajesh2022]

Post out your file to check please.

Attached updated file.

 

[bosco_yip]

Oh, I see. Please use Find function instead of Search function as in:

Cell B1, formula become:

=LOOKUP(1,-FIND(Sheet2!A$2:A$32,Sheet1!A1),Sheet2!B$2:B$32)

 

[bosco_yip]

1] Because: SEARCH function is case-insensitive, while FIND function is case-sensitive

2] Your source data: "Director of Marketing" contain "cto" and " Marketing"

The computer searching formula from left to right.

So,

2.1] SEARCH formula:

• 1st checking, accept "cto" as "CTO" and return "IT"

2.2] FIND formula:

• 1st checking, not accept "cto" as "CTO", and
• 2nd checking, accept the word "Marketing" then return "Marketing"

Therefore

Find function is meet with your requirement.