How to return value by using a list as lookup value

[gwhiz921]

Hi,

I have a list of data and I want to use Vlookup to compare it to another list. Is it possible to use range of data as the lookup_Value?

For example, one list is blue, red, green, yellow, purple.
The other list is the car is green, the car is yellow, the phone is purple, the sky is blue, the fire is red.

I want to find the color in each sentence. Can I input a formula that will allow me to search one cell with the 5 lookup values and return the correct color?

This will save me a lot of time rather than looking for all cells that contain blue, red, etc. one byone.

 

[JGordon11]

Case insensitive:

Book2
	A	B	C	D	E
1	car is green	green		blue
2	the car is yellow	yellow		red
3	the phone is purple	purple		Green
4	the sky is blue	blue		YELLOW
5	the fire is red	red		purple
6	the cat is Felix
7
Sheet3

Cell Formulas
Range	Formula
B1:B6	B1	=TEXTJOIN("",1,IFERROR(MID(A1,SEARCH($D$1:$D$5,A1),LEN($D$1:$D$5)),""))

Case Sensitive:

Book2
	A	B	C	D	E
1	car is green			blue
2	the car is yellow			red
3	the phone is purple	purple		Green
4	the sky is blue	blue		YELLOW
5	the fire is red	red		purple
6	the cat is Felix
7
Sheet3

Cell Formulas
Range	Formula
B1:B6	B1	=TEXTJOIN("",1,IFERROR(MID(A1,FIND($D$1:$D$5,A1),LEN($D$1:$D$5)),""))

 

[gwhiz921]

Hi, this returns a blank cell. The colors are on another sheet, I used the correct sheet destination but should i tbe on same sheet to work?

 

[JGordon11]

In Excel 365 that should work if you change $D$1:$D$5 to something like Sheet2!$D$1:$D$5 in both places in the formula. In earlier versions of Excel you would need to enter the formula with ctrl-shift-enter not just enter.

Book2
	A	B	C
1	car is green	green
2	the car is yellow	yellow
3	the phone is purple	purple
4	the sky is blue	blue
5	the fire is red	red
6	the cat is Felix
7
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=TEXTJOIN("",1,IFERROR(MID(A1,SEARCH(Sheet2!$D$1:$D$5,A1),LEN(Sheet2!$D$1:$D$5)),""))

Book2
	A	B	C	D	E
1				blue
2				red
3				Green
4				YELLOW
5				purple
6
Sheet2