Lookup from Table?

[bkilgore]

I am trying to do a lookup from a table based on 4 criteria. 3 criteria are entered via drop down lists, and one is a constant. See below. I need a formula that will deliver the results in F10. I believe there are several ways to acomplish this, I think with Index And/Or Match, but I am having trouble.

The entire table has a named range of "Costs". This is a little over my head, so hopefully this is not too difficult. Any help is very much appreciated! Thank you!

Below is a sample from the table. The table is much larger.

[TABLE="width: 881"]
<TBODY>[TR]
[TD]A3</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]Billing</SPAN>
[/TD]
[TD][/TD]
[TD]12435180</SPAN>
[/TD]
[TD]12435181</SPAN>
[/TD]
[TD]12435165</SPAN>
[/TD]
[TD]12435173</SPAN>
[/TD]
[TD]12435196</SPAN>
[/TD]
[TD]12435197</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]Region</SPAN>
[/TD]
[TD][/TD]
[TD]East</SPAN>
[/TD]
[TD]East</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[TD]National</SPAN>
[/TD]
[TD]National</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]Area</SPAN>
[/TD]
[TD][/TD]
[TD]NY</SPAN>
[/TD]
[TD]NY</SPAN>
[/TD]
[TD]CA</SPAN>
[/TD]
[TD]CA</SPAN>
[/TD]
[TD]TX</SPAN>
[/TD]
[TD]TX</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]Type</SPAN>
[/TD]
[TD][/TD]
[TD]Project</SPAN>
[/TD]
[TD]Dedicated</SPAN>
[/TD]
[TD]Project</SPAN>
[/TD]
[TD]Dedicated</SPAN>
[/TD]
[TD]Project</SPAN>
[/TD]
[TD]Dedicated</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]ResourceA</SPAN>
[/TD]
[TD][/TD]
[TD]$ 100.00 </SPAN>
[/TD]
[TD]$ 80.00 </SPAN>
[/TD]
[TD]$ 105.00 </SPAN>
[/TD]
[TD]$ 85.00 </SPAN>
[/TD]
[TD]$ 120.00 </SPAN>
[/TD]
[TD]$ 115.00 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]ResourceB</SPAN>
[/TD]
[TD][/TD]
[TD]$ 90.00 </SPAN>
[/TD]
[TD]$ 70.00 </SPAN>
[/TD]
[TD]$ 95.00 </SPAN>
[/TD]
[TD]$ 75.00 </SPAN>
[/TD]
[TD]$ 110.00 </SPAN>
[/TD]
[TD]$ 105.00 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD]ResourceC</SPAN>
[/TD]
[TD][/TD]
[TD]$ 80.00 </SPAN>
[/TD]
[TD]$ 60.00 </SPAN>
[/TD]
[TD]$ 85.00 </SPAN>
[/TD]
[TD]$ 65.00 </SPAN>
[/TD]
[TD]$ 100.00 </SPAN>
[/TD]
[TD]$ 95.00 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD]ResourceD</SPAN>
[/TD]
[TD][/TD]
[TD]$ 70.00 </SPAN>
[/TD]
[TD]$ 50.00 </SPAN>
[/TD]
[TD]$ 75.00 </SPAN>
[/TD]
[TD]$ 55.00 </SPAN>
[/TD]
[TD]$ 90.00 </SPAN>
[/TD]
[TD]$ 85.00 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>
[/TD]
[TD]Crieteria</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Results</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15</SPAN>
[/TD]
[TD]Region</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[TD="colspan: 3"]From Data Validation List</SPAN>
[/TD]
[TD]Rate</SPAN>
[/TD]
[TD]???</SPAN>
[/TD]
[TD="colspan: 3"]Formula to deliver results in F10</SPAN>
[/TD]
[/TR]
[TR]
[TD]16</SPAN>
[/TD]
[TD]Area</SPAN>
[/TD]
[TD]CA</SPAN>
[/TD]
[TD="colspan: 3"]From Data Validation List</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>
[/TD]
[TD]Type</SPAN>
[/TD]
[TD]Project</SPAN>
[/TD]
[TD="colspan: 2"]Always ="project"</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18</SPAN>
[/TD]
[TD]Resource</SPAN>
[/TD]
[TD]ResourceB</SPAN>
[/TD]
[TD="colspan: 3"]From Data Validation List</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Vidar]

Hi

Try this "array" formula (confirm with Ctrl+Shift+Enter)
=INDEX($D$9:$I$12,MATCH($C$18,$B$9:$B$12,0),MATCH($C$15&$C$16&$C$17,$D$5:$I$5&$D$6:$I$6&$D$7:$I$7,0))

Vidar

 

[bkilgore]

This is working in the test table, I will try on the actual table to see what happens.

Is it possible to hard code the criteria in C17, so it does not need to be entered? In other words, so i can select the other 3 criteria and the formula will always use the "Project" selection?

Thank you very much for your response and assistance!!!

 

[Vidar]

Hi

This one has "Project" hardcoded in the formula:
=INDEX($D$9:$I$12,MATCH($C$18,$B$9:$B$12,0),MATCH($C$15&$C$16&"Project",$D$5:$I$5&$D$6:$I$6&$D$7:$I$7,0))
Remember to hit Ctrl+Shift+Enter.

Vidar

 

[bkilgore]

Works great! Thank you very much for your help.

 

[Vidar]

You're welcome