Excel Formula CONTAINS

Question

I need a formula that will look for a specific text in a cell that is also referenced on another sheet and input the value next to it. Kind of like a vlookup but with an if statement. "if text contains value in this other cell, put the corresponding value"

Thanks Albin. Sheet 1 has a list of text fields and a corresponding value next to them: Apple | Green, Cherry | Red, Cucumber | Green, Carrot | Orange.... and Sheet 2 has a cell with a description "The Apple fell from the tree and dropped to the ground", then I would like the cell to return with value "GREEN" — Tiffany, Dec 28 '18 at 00:58
that is not very specific, try adding a screenshot or s.th. similar, and explain the example in the screenshot — Albin, Dec 28 '18 at 00:59
Possible duplicate of MS excel - assigning "categories" based on keywords — Scott - Слава Україні, Dec 28 '18 at 06:59
@Tiffany did you solve your problem with the existing answers? If you did, please accept the appropriate answer. — Albin, Jan 04 '19 at 13:49

score 0 · Answer 1 · answered Dec 28 '18 at 01:41

0

Try this formula:

=IFERROR(LOOKUP(1,0/FIND($A$2:$A$5,E2),$B$2:$B$5),"")

answered Dec 28 '18 at 01:41

Lee

2,963

1

An explanation of how/why this works would make it a more educational answer. :-) – fixer1234 Dec 28 '18 at 03:36

Rajesh Sinha · Answer 2 · 2018-12-28T09:27:12.133

-1

You can try this Formula also:

=INDEX(Sheet1!$B$2:$B$5, SMALL(IF(ISNUMBER(SEARCH($A2, Sheet1!$A$2:$A$5)), MATCH(ROW(Sheet1!$A$2:$A$5), ROW(Sheet1!$A$2:$A$5))), ROWS($A$1:A$1)))

Note,

Write this Formula in Cell B2 of Sheet 2 & fill it Down.
You can adjust Sheet name & Cell references in the formula as needed.

edited Dec 28 '18 at 09:27

answered Dec 28 '18 at 09:12

Rajesh Sinha

9,218
6
17
37

Excel Formula CONTAINS

2 Answers2