Find Partial Match from List??

[svendiamond]

Greetings! I am trying to get the following results in column B:

Book1
	A	B	C	D
1	Values	Exception?		Exceptions
2	JOHN SMITH			UNIVERS
3	UNIVERSITY OF OHIO	1		STATE
4	UNIVERSIDAD DE COLUMBIA	1
5	JAMES JOHNSON
6	HOPKINS UNIVERSITY	1
7	STACY JONES
8	OHIO STATE	1
Sheet6

Basically, if the cell in column A matches any of the list in column D, I want to return a 1. How can I make this happen? I've tried a few different combinations of FIND and MATCH and I can't seem to get it right. I've also tried using Range.Find in VBA to no avail.

 

[T. Valko]

Try this...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][td="bgcolor: #c0c0c0"]

B​

[/td][td="bgcolor: #c0c0c0"]

C​

[/td][td="bgcolor: #c0c0c0"]

D​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td]

Values​

[/td][td]

Exception?​

[/td][td]

------​

[/td][td]

Exceptions​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td]

JOHN SMITH​

[/td][td][/td][td][/td][td]

UNIVERS​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

3​

[/td][td]

UNIVERSITY OF OHIO​

[/td][td]

1​

[/td][td][/td][td]

STATE​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

4​

[/td][td]

UNIVERSIDAD DE COLUMBIA​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

5​

[/td][td]

JAMES JOHNSON​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

6​

[/td][td]

HOPKINS UNIVERSITY​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

7​

[/td][td]

STACY JONES​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

8​

[/td][td]

OHIO STATE​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[/table]

This formula entered in B2 and copied down:

=IF(COUNT(LOOKUP(1E100,SEARCH(D$2:D$3,A2))),1,"")

 

[njimack]

Confirm with Ctrl+Shift+Enter:
=--ISNUMBER(SEARCH($D$2:$D$3,A2))

 

[svendiamond]

Thank you! That is awesome. Could you explain how this LOOKUP function is working? I rarely ever use it. What is the 1E100 for:? EDIT: found in link, thank you everyone!

 

[MickG]

Resolved !!!

 

[T. Valko]

See this...

specific text in a cell should return a text in another cell

 

[svendiamond]

Awesome. Thank you guys.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[svendiamond]

Resolved !!!

MickG, did you also post some VBA code? I was looking at it and I think you removed it on your edit. Can you post it again? I think a VBA solution might be better and I was going to try yours out...