Connecting HR info - diff tabs, IF criteria, etc.

Hi, all, probably not super complex but not sure where to start.

Work is being done on tab named Sheet1, Reference worksheet tabs are named 500 to 584

Each reference worksheet has a text string in A2 that corresponds to that tab number. Example: Tab 500, A2 = IT Branch, Tab 502, A2=Health Services Section , etc.

Sheet1 has a list of all the employees by last name in column B, and in column A there is a position # for each person which includes that corresponding tab number (example: 332-500-4723-002).

All I want to do is add another column to Sheet1 which has the corresponding String name for their sections (so if it sees 500 as part of the number in column A, the function finds the right tab by its number, then enters the same value as A2 in a new specified column on sheet 1)


Basically I'm trying to get the section name for each employee on worksheet1 without having to do some kind of "find and replace" manually 84 times (but in a new column).