VLOOKUP with multiple search values?

rej

Hi all, 

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

Values:

• 1|Lion|Project Lead|John Doe
• 2|Tiger|Project Support|John Doe

 

Sheet "Timesheet"

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?

Thanks

REJ

Shaine Greenwood

Hi REJ,

You might be able to wrap one VLOOKUP function inside of another to accomplish this. For example: 

=VLOOKUP(VLOOKUP("Project 1", [Project Number]1:Role3, 2, false), [Assigned to]1:Role3, 2, false)

I tested the formula above out on a small table (attached screenshot) to get the expected result. I was using direct text string values, so you might instead want to use cell references instead of "Project 1" in your formula.

One key factor in this is that the primary VLOOKUP function in this formula—the outside-most function—needs to reference a smaller table that begins with the Assigned to column. Your inside VLOOKUP should reference Project Number, Assigned to, and Role; your outside VLOOKUP should reference Assigned to and Role.