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

	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​

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