Associating the content in a Column with data in another column then concatenat the two in

I have a very simple request that is absolutely baffling me: Here is the basic sample:

I have a workbook that is divided into three sheets. The first sheet is my dashboard, the second sheet is all of my raw data, the third sheet contains all of the data I use for drop-downs and data validation in the second sheet.

In the third sheet I have a table named Agency. It contains two columns; Column 1 contains the Agency name spelled out and Column two contains the abbreviation for that agency. One of the column in my second sheet is for Agencies and is a data validation drop down that pulls from the Abbreviation column in the Agency table located on sheet three.

What I would like to do is:

On sheet two, when I make a selection in the Agency column it associates the abbreviation I selected with the full name of the agency, then concatenates both of them into a third cell.

Something like this:

Sheet 2 Column D>Select "ADOT" excel associates "ADOT" with the full name of the agency in the ADOT row in sheet three and concatenates both into another field on sheet two. The result looks like this:

Sheet Two:

Col D Col F
ADOT Alaska Department of Transportation ADOT

Sample Workbook (Excel_Forum).xlsx

Here is a sample work book

Thank you so much. It worked like a charm.

=VLOOKUP(D2,CHOOSE({1,2},Table2[Abbreviation],Table2[Name]),2,0)&" ("&D2&")"

A few questions:

What does {1,2} reference? My guess is the sheets?

What does ,2,0 reference? No guess

The actual workbook I'm using hase different sheet names, so I'm wondering where to revise the formula.