Search a cell against a range to see if a value from the range is present and if so, return that value

[mdsurf]

Hi everyone! I'm trying to search a cell against a range to see if a value from the range is present and if so, return that value:

Example:

Cells To Search For Value	Equation to search cells agains Range
This is my new car for the year	new car
Did she buy the house for her family	buy the house
What time is the doctors appointment in the city	doctors appointment

Range

doctors appointment

buy the house

new car

Basically what I want to do in the second column is have an equation that searches the first column to see if a value from the range is present and if so, return that value.

Any help is greatly appreciated!

 

[6StringJazzer]

My solution uses an array formula.

=CONCAT(IF(ISERROR(FIND($A$7:$A$9,A2)),"",MID(A2,FIND($A$7:$A$9,A2),LEN($A$7:$A$9))))

This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

 

[mdsurf]

Hi Jeff, thank you so much for helping me out with this. Do you know if it's possible to do it without VBA? I've never used VBA before so it might be a bit above my skill level so wondering if there's another way.

Either way I very much appreciate your time / help on this.

Thank you!

 

[6StringJazzer]

My solution is a formula, not VBA.

 

[jtakw]

Hi,

Another formula solution:

Book3.xlsx
	A	B
1	Cells To Search For Value	Equation to search cells agains Range
2	This is my new car for the year	new car
3	Did she buy the house for her family	buy the house
4	What time is the doctors appointment in the city	doctors appointment
5
6	Range
7	doctors appointment
8	buy the house
9	new car
Sheet972

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(LOOKUP(2,1/SEARCH(A$7:A$9,A2),A$7:A$9),"")