VLOOKUP with multiple search values?
I like to streamline the project time sheets for my employees. I have two sheets: 1) A Master Project sheet, and a 2) Timesheet:
Sheet "Master projects"
Project No. | Project Name | Role | Team Member
- 1|Lion|Project Lead|John Doe
- 2|Tiger|Project Support|John Doe
Project No. | Project Name | Role | Team Member | Date | Time
Values via Form:
- (Drop Down) | (VLOOKUP) | (VLOOKUP) | (Predefined in Form) | (Selected in Form) | (Selected in Form)
So the goal is that a team member has only to select the Project No, the Date and Time - all other fields shall be calculated automatically.
The Challenge: The "Role" is not selected correctly. When John Doe fills out the form for Project 2 (=Tiger) the Role "Project Lead" will be selected as VLOOKUP takes the first "Role" of John Doe.
Idea: I would like that Smart sheet looksup first the Project No., and then within the Project No, checks which role the team member has.
How is this possible?