How to return value by using a list as lookup value

gwhiz921

New Member
Joined
Sep 29, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
367
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Case insensitive:

Book2
ABCDE
1car is greengreenblue
2the car is yellowyellowred
3the phone is purplepurpleGreen
4the sky is blueblueYELLOW
5the fire is redredpurple
6the cat is Felix 
7
Sheet3
Cell Formulas
RangeFormula
B1:B6B1=TEXTJOIN("",1,IFERROR(MID(A1,SEARCH($D$1:$D$5,A1),LEN($D$1:$D$5)),""))


Case Sensitive:

Book2
ABCDE
1car is green blue
2the car is yellow red
3the phone is purplepurpleGreen
4the sky is blueblueYELLOW
5the fire is redredpurple
6the cat is Felix 
7
Sheet3
Cell Formulas
RangeFormula
B1:B6B1=TEXTJOIN("",1,IFERROR(MID(A1,FIND($D$1:$D$5,A1),LEN($D$1:$D$5)),""))
 

gwhiz921

New Member
Joined
Sep 29, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 18, 2021
Messages
367
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABC
1car is greengreen
2the car is yellowyellow
3the phone is purplepurple
4the sky is blueblue
5the fire is redred
6the cat is Felix 
7
Sheet1
Cell Formulas
RangeFormula
B1:B6B1=TEXTJOIN("",1,IFERROR(MID(A1,SEARCH(Sheet2!$D$1:$D$5,A1),LEN(Sheet2!$D$1:$D$5)),""))


Book2
ABCDE
1blue
2red
3Green
4YELLOW
5purple
6
Sheet2
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,115
Messages
5,768,194
Members
425,459
Latest member
Danniey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top