Two tables on separate worksheets - need result of text in worksheet 1

jlr123

New Member
Joined
Mar 6, 2014
Messages
38
I have two tables on separate worksheets in a workbook. Need formula to match column B in
worksheet 1 with column B in worksheet 2 then return the text that is in column A in worksheet 2 into a new column (D) on worksheet 1.
Thanks so much!
 

Excel Facts

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,439
Office Version
  1. 2016
Platform
  1. Windows
Hi JLR123,

Here's Worksheet2
JLR123.xlsx
AB
1TargetMatch
2WoofDog
3MeowCat
4GrowlHippo
5NaaGoat
Worksheet2


Here's Worksheet1 with the formula.
JLR123.xlsx
BCD
1MatchResult
2DogWoof
3CatMeow
4Sheep 
5GoatNaa
6Mole 
Worksheet1
Cell Formulas
RangeFormula
D2:D6D2=IFERROR(INDEX(Worksheet2!A:A,MATCH(B2,Worksheet2!B:B,0)),"")
 

jlr123

New Member
Joined
Mar 6, 2014
Messages
38

ADVERTISEMENT

Ok when I double click on the formula, pop up window takes me to a window "requesting" another file name. The data is in one workbook and two worksheets.

Worksheet 1
Column A Column B Column C Column D
SP Cust No Cust Name Worksheet 2 SP
ZZ1 0102.00 XYZ Corp BSB

Worksheet 2
SP Cust No Cust Name Column D
BSB 0102.00 XYZ Corp Blank

Thanks.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,439
Office Version
  1. 2016
Platform
  1. Windows
Are you sure the sheets are named correctly?
I've added the space between Worksheet and number on this:
JLR123.xlsx
AB
1TargetMatch
2WoofDog
3MeowCat
4GrowlHippo
5NaaGoat
Worksheet 2


JLR123.xlsx
BCD
1MatchResult
2DogWoof
3CatMeow
4Sheep 
5GoatNaa
6Mole 
Worksheet 1
Cell Formulas
RangeFormula
D2:D6D2=IFERROR(INDEX('Worksheet 2'!A:A,MATCH(B2,'Worksheet 2'!B:B,0)),"")
 

jlr123

New Member
Joined
Mar 6, 2014
Messages
38

ADVERTISEMENT

How can I send the excel workbook to you?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,439
Office Version
  1. 2016
Platform
  1. Windows
The forum requires that any questions are answered in the forum so you could make sure there is no confidential information contained and post a link here to the Dropbox version.

If you do want to email it to me (and all you need is my handle and know I use Google email) then I will post at least a subset of the data here so others can work on your question.
 

jlr123

New Member
Joined
Mar 6, 2014
Messages
38
Unfortunately, I do not have Dropbox. Please reply with email address and I will send to you a non-confidential file to you.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,439
Office Version
  1. 2016
Platform
  1. Windows
I don't want to post it here in case bots scan it but it's my handle and I use Google email.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,485
Messages
5,636,608
Members
416,929
Latest member
Nitil

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