I have two separate sheets I'm working out of; on sheet1 I have a list of names, in the column next to the names I need to pull a value from Table2 located on sheet2.

The trick is that I need to match the names from my list on sheet 1 with the list from sheet2 table2 and provide me the data from the cell to the right (on table2) corresponding with the name I'm identifying on sheet1

I've tried mixing and matching different iterations of index,match,offset functions but I can't seem to figure out something I thought I would be able to easily figure out. Maybe I'm just running myself in circles and confusing myself by making it harder than it has to be. Please help me out; and thank you in advance.

I tried this as my final (logical) in-cell code (where rbs_2014 is the column in table 2 with the names I'm trying to match from sheet1... but of course it didn't work:

=INDEX(Table2,MATCH(D4,rbs_2014,-1),OFFSET(rbs_2014,0,1))