CODEWITHSUNDEEP
excelformulas
Dan B
Dan B

Reputation: 5

Formula to find part of a cell's value as exact match to a value from other cells

On tab 1, I have data down column B that contains string values like this:

\\ABC\VOL1\DATA\Dan\Personal Folders
\\ABC\VOL1\DATA\Mike\My Stuff\Docs\Support
\\ABC\VOL1\DATA\Mike\My Stuff\Photos
\\ABC\VOL1\DATA\Bob\Plans

On tab 2, I have data down column C that I want to use as a lookup table like this:

\\ABC\VOL1\DATA\Adam
\\ABC\VOL1\DATA\Steve
\\ABC\VOL1\DATA\Mike
\\ABC\VOL1\DATA\Ronnie

I need a formula I can put on tab 1 down column C to see if any part of each text string from tab 1, column B matches any complete text string from tab 2, column C. So for example, I would place this formula to show Yes or No like this:

Column B......................................................................Column C
\\ABC\VOL1\DATA\Dan\Personal Folders....................No
\\ABC\VOL1\DATA\Mike\My Stuff\Docs\Support..........Yes
\\ABC\VOL1\DATA\Mike\My Stuff\Photos.....................Yes
\\ABC\VOL1\DATA\Bob\Plans.......................................No

I have tried VLOOKUP, INDEX/MATCH, ISNUMBER/SEARCH, and COUNTIF as an array with wildcards, but I just cannot seem to figure it out. Currently, with COUNTIF as array, I have this:

=COUNTIF(B2,"*" & 'Tab 2'!$C$2:$C$1000 & "*")

This is the start to return the array results, but the results aren't as I would expect as I get "0" when I should get "1" for certain rows.

If anyone can help me out with a formula (not VBA) using any of the functions mentioned above or any others, I would greatly appreciate it. Thanks.

Upvotes: 0

Views: 1373

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

use this array formula:

=IF(COUNT(SEARCH('Tab 2'!$C$2:INDEX('Tab 2'!$C:$C,MATCH("zzz",'Tab 2'!$C:$C)),B2)),"Yes","No")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 1

dougiek
dougiek

Reputation: 13

This will search the cell for any of your criteria and return the number of matches if there is a match (which unless you have duplicates in the criteria it will be a 1).

Enter as array.

{=COUNT(SEARCH($C$1:$C$4,B1))}

Upvotes: 1

Related Questions