Help with VLOOKUP formula to pull data from another sheet

[fyrmest]

Hello. I am working with Latin vocabulary lists. I have one list in a sheet named “Bridge” and a second in a sheet named “Dwane”. There are columns in both sheets named “SIMPLE_LEMMA”. I would like to match multiple values from the “SIMPLE_LEMMA” column and pull the data values from a column in the “Dwane” sheet that does not exist in the “Bridge” sheet. In other words, import this “DERIVATIVES” column into the “Bridge” sheet, matching up on the corresponding “SIMPLE_LEMMA” row.

In the “Bridge” tab, the “SIMPLE_LEMMA” column is column B, the second column, and I would pull the data values into column M (13th column). In the “Dwane” sheet, the “SIMPLE_LEMMA” column is column B (second), and the “DERIVATES” column is column E (the 5th column).
Is there a functions maven out there for whom this might be a cinch? Thanks in advance if you can help.

[JohnSUN]

It will be

=VLOOKUP(B2;Dwane.$B$1:$E$1000;4;0)

or

=INDEX(Dwane.E$1:E$1000;MATCH(B2;Dwane.$B$1:$B$1000;0))

or

{=TEXTJOIN(",";1;IF($Dwane.B1:B1000=B2;$Dwane.E1:E1000;""))}

(Pay attention to the curly brackets around the last formula - this is an array formula, it must be entered by pressing Ctrl+Shift+Enter instead of the usual Enter)

[fyrmest]

Thanks, @JohnSUN. The first formula worked like a charm. This has saved me lots of time of manual input!

[JohnSUN]

@fyrmest In fact, all three formulas will work. And to be honest, the third formula seems to me the best. If the first two formulas find one and only one match, then the last formula tries to find ALL matches. Since we are talking about a dictionary, we should assume that the lists are very, very long. And this means that sooner or later, due to ordinary human inattention, you will add two or more words to SIMPLE_LEMMA, for which you will specify different DERIVATES. If (when) this happens, the formula will show all DERIVATES for this value, separated by commas - you will immediately see that there are duplicates in the table.

[fyrmest]

Yes, you are correct @JohnSUN. The VLOOKUP formula just pulled the one derivative form from the second sheet. And I had to copy the formula to 1,600 lines of entries. But this was still better than the manual input. Thanks again.