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

Role v / Rate Type > <colgroup><col></colgroup><tbody> </tbody>	T	4	C
Leader 1	$0	$0	$400
Leader 2	$264	$538	$0

<tbody>
</tbody>

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.

Name	Staff Level	Resource Type	Rate
Joe Schmo	Leader 1	C	#NAME?
John Doe	Leader 2	T	#REF!

<colgroup><col width="69" span="2" style="width:52pt"> <col width="69" style="width:52pt"> <col width="69" style="width:52pt"> </colgroup><tbody>
</tbody>

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