Look up matching values from 2 different workbooks

[datastudent]

Hi,

I need to match values to names from 2 different spreadsheets. It’s data I downloaded that contains values that need to be entered into a main spreadsheet. The data are on multiple tabs on the 2nd workbook
I just need to match the names to the values so I don’t have to do this manually. Vlookup and IMatch does work but only if the data is all in one tab. I need a formula that will allow me to look up from all tabs from a different workbook.

Main Spreadsheet. Column D is the data that came from the 2nd workbook.

Sample of the tabs in the 2nd workbook. Tabs are categorized per LOB.

Thanks!