• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to compare cell value with another column

rajesh2022

New Member
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.
 

Attachments

rajesh2022

New Member
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

Excel Ninja
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:

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)
 

rajesh2022

New Member
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

Excel Ninja
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)

82434
 

bosco_yip

Excel Ninja
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.
 
Last edited:
Top