Lookup text string in cell, then return a corresponding value from another worksheet

CF64

New Member
Joined
Feb 17, 2021
Messages
48
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have 2 worksheets.
Worksheet 1 contains a column of cells which contains multiple strings of text
Worksheet 2 contains a table with 2 columns. Column 1 contains text which matches what appears in Column A of Worksheet 1, and Column 2 contains some other text

What I am hoping someone can help me with is a formula to insert in Worksheet 1 - Column B, which will reference the cells in Worksheet 2 Column 1, and return the value in column 2
if cell in worksheet 1 column A contains "Apple", return "red" (from column 2 in worksheet 2) in column B of worksheet 1
if cell in worksheet 1 column A contains "Orange", return "navel" (from column 2 in worksheet 2) in column B of worksheet 1
if cell in worksheet 1 column A contains "banana", return "yellow" (from column 2 in worksheet 2) in column B of worksheet 1
etc.

Worksheet 1
AB
apple
orange
bannana
grapes
strawberry
blueberry
orange
banana
grapes
strawberry
blueberry
apple
banana
grapes
strawberry

Worksheet 2
12
applered
orangenavel
bannanayellow
grapesgreen
strawberrywhite

Any guidance is greatly appreciated
Thank you
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi, I just realized an error in my table examples. The cell in worksheet 1 column A would only contain 1 of the values in the table in Worksheet 2 Column 1.

if cell in worksheet 1 column A contains "Apple", return "red" (from column 2 in worksheet 2) in column B of worksheet 1
if cell in worksheet 1 column A contains "cherry", return "tart" (from column 2 in worksheet 2) in column B of worksheet 1
if cell in worksheet 1 column A contains "kiwi", return "ripe" (from column 2 in worksheet 2) in column B of worksheet 1
if cell in worksheet 1 column A doesn't contain any of the values in Worksheet 2 column 1, return blank in column b of worksheet 1

Worksheet 1
AB
apple
orange
bannana
grapes
strawberry
blueberry
orange
bannana
grapes
strawberry
blueberry
cherry
bannana
grapes
strawberry

Worksheet 2
12
applered
cherrytart
rasberrysweet
kiwiripe

Apologies for any confusion.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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
Back
Top