INDEX formula help

[alexander.small]

I am looking to improve an existing spreadsheet so that it can become a lot more flexible for the users.

In one column there is currently the formula below that looks inputs a team name into the cells in a column if the name in that row matches up with the name in a table on another sheet. The name of the team is also in the other sheet which is part of a drop down list.

=IF(OR(ISERROR(INDEX(TABLE[Team],MATCH([User],TABLE[User],0))),[User]=""),"",INDEX(TABLE[Team],MATCH([User],TABLE[User],0)))


Some of the other information in the spreadsheet is the months in which projects were completed in the different teams by different users. It has got to the stage now where some of the users have moved between different teams and therefore have exactly the same details but for two different teams.
I have atemporary solution for this and that is to put the value "2" next to the name so that Excel recognises it as a completely different input. I am looking for a permanent solution so that I can have the same user in the same spreadsheet but with the project hours logged beside two different team names.

I apologise if I have made this sound much more confusing than it needs to be, if I have made anything unclear please comment and I will try to explain a bit more clearly.

Thank you to anyone that can provide me with some help

Alexander Small

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[alexander.small]

Thank you for your help!

Is there no way to do this but just by adding another condition onto the end of the existing formula?

[XOR LX]

Hi,

I imagine there will be. But the point is that not many forum members are willing to construct their own dummy data in order to test potential solutions, hence the request for you to upload a sample sheet.

Regards