LOOKUP Help

mrkris1982

Active Member
Joined
Apr 16, 2009
Messages
407
Column B: List of months
Column C: List of applications
Column F: Total Hours

In column I, I have data validation list pointing to the months in column B
In column J, I have a data validation list pointing to the apps in column C
In column K, I want the total time in column F to appear here based on what is selected in column I and J

Scenario: I select January in Column I......Select App2 in column J....Column K should automatically return a value of 496 hours (since that is defined in column F) and I feel this is done through a lookup but not sure. Any advice on how to write the lookup? In English it would basically say "Show me the total hours from a chart when any combination of a month and application is chosen"


Once this is resolved, will I be able to perform calculations off the number returned in column K? So in column L, I'd enter a number....in column M would subtract column L from K and show a total.

Thanks in advance.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
How's this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Months</td><td style="text-align: center;;">Apps</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Total Hours</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Month</td><td style="text-align: center;;">App</td><td style="text-align: center;;">Hours</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Jan-16</td><td style="text-align: center;;">a</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Apr-16</td><td style="text-align: center;;">b</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Jan-16</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Jan-16</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Feb-16</td><td style="text-align: center;;">a</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Feb-16</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Feb-16</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Feb-16</td><td style="text-align: center;;">d</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Mar-16</td><td style="text-align: center;;">a</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Apr-16</td><td style="text-align: center;;">a</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Apr-16</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">Apr-16</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">Apr-16</td><td style="text-align: center;;">d</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K2</th><td style="text-align:left">=SUMIFS(<font color="Blue">F:F,B:B,I2,C:C,J2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,365
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top