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

[jlr123]

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!

 

[Toadstool]

Hi JLR123,

Here's Worksheet2

JLR123.xlsx
	A	B
1	Target	Match
2	Woof	Dog
3	Meow	Cat
4	Growl	Hippo
5	Naa	Goat
Worksheet2

Here's Worksheet1 with the formula.

JLR123.xlsx
	B	C	D
1	Match		Result
2	Dog		Woof
3	Cat		Meow
4	Sheep
5	Goat		Naa
6	Mole
Worksheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(INDEX(Worksheet2!A:A,MATCH(B2,Worksheet2!B:B,0)),"")

 

[jlr123]

Does not work.

 

[Toadstool]

Does not work.

Gives an error?
Gives unexpected result?
Won't let you enter the formula?

 

[jlr123]

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]

Are you sure the sheets are named correctly?
I've added the space between Worksheet and number on this:

JLR123.xlsx
	A	B
1	Target	Match
2	Woof	Dog
3	Meow	Cat
4	Growl	Hippo
5	Naa	Goat
Worksheet 2

JLR123.xlsx
	B	C	D
1	Match		Result
2	Dog		Woof
3	Cat		Meow
4	Sheep
5	Goat		Naa
6	Mole
Worksheet 1

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(INDEX('Worksheet 2'!A:A,MATCH(B2,'Worksheet 2'!B:B,0)),"")

 

[jlr123]

How can I send the excel workbook to you?

 

[Toadstool]

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]

Unfortunately, I do not have Dropbox. Please reply with email address and I will send to you a non-confidential file to you.

 

[Toadstool]

I don't want to post it here in case bots scan it but it's my handle and I use Google email.