Lookup: If a cell contains a string, return a value from an array

[aumabrey]

I'm curious about the best way to search a cell, and if a specific string is found then return a specific value based on an array of outputs. Here's the current formula that I'm trying and I'm not sure what the issue is (I'm on Excel 2019 for Mac if that is a potential issue):

=INDEX($D$2:$D$5,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$5,A2)),0))

Basically, I would like to search through A1, if a value from column C is found, the return the corresponding value from Column D into column B.

This is my first post, so hopefully I didn't break any rules or guidelines too horrendously, and apologies if I missed anything.

Thanks!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D
1	Sample	Output	Key	Output Array
2	Red wine on the carpet	Found Red	Red	Found Red
3	Blue -like the color or the mood	Found Blue	Blue	Found Blue
4	yellow -testing if capitalization matters	no match	Yellow	Found Yellow
5	xxgreenxx if the string needs to be isolated by whitespace or delimiters	no match	Green	Found Green
6	Red	Found Red
7	Blue	Found Blue
8	Green	Found Green
9	Yellow	Found Yellow
10
Sheet14

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(LOOKUP(2,1/FIND(" "&$C$2:$C$5&" "," "&A2&" "),$D$2:$D$5),"no match")

If you DON'T want capitalization to matter, use SEARCH instead of FIND.

 

[aumabrey]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D
1	Sample	Output	Key	Output Array
2	Red wine on the carpet	Found Red	Red	Found Red
3	Blue -like the color or the mood	Found Blue	Blue	Found Blue
4	yellow -testing if capitalization matters	no match	Yellow	Found Yellow
5	xxgreenxx if the string needs to be isolated by whitespace or delimiters	no match	Green	Found Green
6	Red	Found Red
7	Blue	Found Blue
8	Green	Found Green
9	Yellow	Found Yellow
10
Sheet14

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(LOOKUP(2,1/FIND(" "&$C$2:$C$5&" "," "&A2&" "),$D$2:$D$5),"no match")

If you DON'T want capitalization to matter, use SEARCH instead of FIND.

Thanks Eric!

This is a good thought, unfortunately the data isn't always consistent with delimiters.... For example some months it may be "Blue - like the..." others it may be "Blue-like the..."

=IFERROR(LOOKUP(2,1/SEARCH($C$2:$C$5,A2),$D$2:$D$5),"no match")

I modified it to this to remove the leading and trailing whitespace, but this was a an immense help! Thanks for the speedy solution!!!

 

[Eric W]

Happy to help! Thanks for the feedback. ?

 

[MrJimC]

Just found this message board - thanks so much for the answer here @EricW! A huge help.

 

[topi1]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D
1	Sample	Output	Key	Output Array
2	Red wine on the carpet	Found Red	Red	Found Red
3	Blue -like the color or the mood	Found Blue	Blue	Found Blue
4	yellow -testing if capitalization matters	no match	Yellow	Found Yellow
5	xxgreenxx if the string needs to be isolated by whitespace or delimiters	no match	Green	Found Green
6	Red	Found Red
7	Blue	Found Blue
8	Green	Found Green
9	Yellow	Found Yellow
10
Sheet14

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(LOOKUP(2,1/FIND(" "&$C$2:$C$5&" "," "&A2&" "),$D$2:$D$5),"no match")

If you DON'T want capitalization to matter, use SEARCH instead of FIND.

@Eric W What if the red, blue, yellow or green in column A are not in the beginning of the string but are somewhere in the middle of the string? Thank you.
PS Sorry, found out what I was doing wrong.