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).

If your position numbers always start with that format (3 numbers then hyphen then a three-digit code for the sheet), then put this in D2 and drag down:

Formula:

Please Login or Register  to view this content.

The first A2 refers to the A2 on Sheet 1, the second to the relevant tab (just in case there's any confusion).

Edit: If the position number format might change, there'll need to be a 'Find' function embedded in the 'Mid' function - not too hard, but it would make the formula look a lot more complicated and there's no point if it's not needed.

ps Welcome to the forum.

You're welcome, glad I could help. Thanks for the rep and for marking the thread as Solved.