Autofill Multiple Columns Based on Table Data

[jmccarron]

I am trying to come up with a solution to autofill multiple cells based on text input to a specific cell.

I want to be able to type in a department prefix into column F and then have columns G,H, and D filled in based on data in a table on a separate tab:

Ultimately, I am trying to find a way that will allow me, to fill in the college, school, and department data based on the 3 digit course prefix in column F.  (In this case, ACC should return: College of Liberal Arts and Business, Dunham School of Business, Accounting).  I've tried Vlookups, Index and Match, and drop downs with data validation, but I can't seem to get anything to work.  Am I trying to bite off too much in a single function?  Any help would be very much appreciated.  

[#2]

@jmccarron 

Any of lookup functions shall work. Perhaps you may share small sample file with the table from which you try to pickup data.

[#3]

@Sergei Baklan 

Hello Sergei,

Thank you for your response.  I am sure the lookup functions will work, it is most likely that I am making a mistake in my formula.  Here is a link to a small sample file:

Sample Worksheet Course Data.xlsx

[#4]

@jmccarron 

Unfortunately you didn't share the file for everyone, it requires credentials.

[#5]

Ah, I was worried about that. The permissions are locked by our organization. This should hopefully work:
https://www.dropbox.com/scl/fi/apfkvgcme53jgrymjp5uf/Sample-Worksheet-Course-Data.xlsx?rlkey=75uimtn...

[#6 · Accepted solution]

@jmccarron 

In G2:

=XLOOKUP(F2, Table3[Course Prefix], Table3[[College]:[Department]], "")

Fill down.

[#7]

That worked beautifully. Thank you, Hans!