Multiple condition lookup

[navi_jen]

Hi, this forum is awesome. I have researched my problem, and cannot find the correct resolution, so here goes:

I need to be able to insert a employee hourly rate into a table. The employee hourly rate is identified by using a combination of 2 variables.

Here is the source table with the roles (leader 1, leader 2) in the column A, rates by rate type (Rate types are T, 4 and C) are noted in Columns B, C, D
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Role v / Rate Type >[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]T[/TD]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Leader 1[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]Leader 2[/TD]
[TD]$264[/TD]
[TD]$538[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have resources assigned to a Role and Rate Type in a separate Budget table that includes Names, Roles, Role/Rate Type . I need to use a vlookup type function to look at role and role/rate type, and insert the correct value (for example, $400, $264 or $538) into the Budget Table.

I have tried several approaches and cannot get them to work. I have base MS Office 2016, not MS Office 365 2016.

[TABLE="width: 276"]
<colgroup><col width="69" span="2" style="width:52pt"> <col width="69" style="width:52pt"> <col width="69" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 69"]Name[/TD]
[TD="class: xl65, width: 69"]Staff Level[/TD]
[TD="class: xl75, width: 69"]Resource Type[/TD]
[TD="class: xl65, width: 69"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 69"]Joe Schmo[/TD]
[TD="class: xl69, width: 69"]Leader 1[/TD]
[TD="class: xl70, width: 69"]C[/TD]
[TD="class: xl74, width: 69"]#NAME?[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 69"]John Doe[/TD]
[TD="class: xl69, width: 69"]Leader 2[/TD]
[TD="class: xl70, width: 69"]T[/TD]
[TD="class: xl74, width: 69"]#REF![/TD]
[/TR]
</tbody>[/TABLE]
In this example, the formula should insert $400 in the cell which currently has the value '#name' and insert $264 in the cell which currently have the value '#value'.

Thanks!

 

[navi_jen]

Re: Multiple condition lookup...help!

If it helps, there are 32 different combinations of role and role/rate type. So I need a fairly robust calculation.

Thanks

 

[kweaver]

Re: Multiple condition lookup...help!

What about:

 

[navi_jen]

Re: Multiple condition lookup...help!

Thank you...it works if the staff level is the value in the first row in the table (Leader 1), but has N/A for any other value (Leader 2).

N/A

Sorry, switched the visuals in this thread to the structure and data in my live sheet. Hope this isn't too confusing.

https://ibb.co/Czdc7pg

 

[kweaver]

Re: Multiple condition lookup...help!

Before ", False" you need " ,0) " because the match needs an exact match and close the match function.
And too many closing parens.

I think it should look like this:

=VLOOKUP(E3,$A$201,MATCH(F3,$A$201:$E$201,0),FALSE)

 

[navi_jen]

Re: Multiple condition lookup...help!

Before ", False" you need " ,0) " because the match needs an exact match and close the match function.
And too many closing parens.

I think it should look like this:

=VLOOKUP(E3,$A$201,MATCH(F3,$A$201:$E$201,0),FALSE)

You are the guru! it worked!

Thanks