vlookup from different sheets depending on a specific value?

[jfarlow]

Hi

I have a spreadsheet with one master sheet which contains a list of all employees and a column stating whether they belong to Group A, Group B, Group C etc.

There is then a separate sheet for each of those groups, which bonus recommendations will be entered into.

What I want to do is do a vlookup in the master sheet of the employee ID to return the bonus recommendation, but I want it to check which group they are in in the master sheet and then do the lookup in the corresponding sheet.
At the moment I've just got nested if's but there could be quite a few groups so that would get messy.

Any suggestions?
thanks

[DonkeyOte]

You would need to use INDIRECT -- assuming the Group column value equates exactly to the worksheet name in question (else you would need another lookup table or lots of IFS which would defeat the purpose right ?)

eg: =VLOOKUP(namecell,INDIRECT("'"&YourGroupCell&"'!A:B"),2,0)

NOTE: INDIRECT is Volatile though (see link in Sig for more info).