Help Excel Formula - lookup with 3 criteria?

C

coliemom

New Member
Joined
Jun 23, 2014
Messages
4
I have a data table with 6 columns:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Week#[/TD]
[TD]Associate[/TD]
[TD]Area[/TD]
[TD]Promises[/TD]
[TD]Hrs[/TD]
[/TR]
[TR]
[TD]01/01/14[/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]North Shore[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]01/15/14[/TD]
[TD]3[/TD]
[TD]Ian[/TD]
[TD]Aberdeen[/TD]
[TD]5[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]01/01/14[/TD]
[TD]1[/TD]
[TD]Lewis[/TD]
[TD]Aberdeen[/TD]
[TD]2[/TD]
[TD].25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How would you return the "Promises" if the date, associate and area all match the criteria.
I can't find the right combination of IF, VLOOKUP, INDEX and MATCH. Or maybe there's another way around I'm not seeing?

Thanks.
 
Sort by date Sort by votes
it sounds like you would probably want to try using a sumifs or sumproduct.

Using the data in this post as an example, if there were multiple values meeting the criteria and you needed to add them up then something like...

Excel 2012
ABCDEF
JaneNorth Shore
IanAberdeen
LewisAberdeen
IanAberdeen
LewisNorth Shore
LewisAberdeen
LewisAberdeen

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Date[/TD]
[TD="bgcolor: #FFFF00"]Week#[/TD]
[TD="bgcolor: #FFFF00"]Associate[/TD]
[TD="bgcolor: #FFFF00"]Area[/TD]
[TD="bgcolor: #FFFF00"]Promises[/TD]
[TD="bgcolor: #FFFF00"]Hrs[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/15/2014[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFF00"]Date[/TD]
[TD="bgcolor: #FFFF00"]Associate[/TD]
[TD="bgcolor: #FFFF00"]Area[/TD]
[TD="bgcolor: #FFFF00"]HRS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/1/2014[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D12[/TH]
[TD="align: left"]=SUMIFS(F2:F7,D2:D7,C12,C2:C7,B12,A2:A7,A12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Weazel said:
it sounds like you would probably want to try using a sumifs or sumproduct.

Using the data in this post as an example, if there were multiple values meeting the criteria and you needed to add them up then something like...

Excel 2012
ABCDEF
JaneNorth Shore
IanAberdeen
LewisAberdeen
IanAberdeen
LewisNorth Shore
LewisAberdeen
LewisAberdeen

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Date[/TD]
[TD="bgcolor: #FFFF00"]Week#[/TD]
[TD="bgcolor: #FFFF00"]Associate[/TD]
[TD="bgcolor: #FFFF00"]Area[/TD]
[TD="bgcolor: #FFFF00"]Promises[/TD]
[TD="bgcolor: #FFFF00"]Hrs[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/15/2014[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFF00"]Date[/TD]
[TD="bgcolor: #FFFF00"]Associate[/TD]
[TD="bgcolor: #FFFF00"]Area[/TD]
[TD="bgcolor: #FFFF00"]HRS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/1/2014[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D12[/TH]
[TD="align: left"]=SUMIFS(F2:F7,D2:D7,C12,C2:C7,B12,A2:A7,A12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Yes, this work very well. Sometimes I overcomplicate things and the simple solution is usually the best. Thanks for your help Weazel.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Formula to identify date and location from a table and return value
Replies
3
Views
78
Fluff
Fluff
L
  • Solved
EXCEL FORMULA TO COUNT
Replies
6
Views
316
LAY2022
L
staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
528
staticfluids
staticfluids
D
  • Question Question
How to Copy rows from one sheet to another based on the criteria in the second sheet
Replies
9
Views
247
dsubash
D
M
  • Question Question
Dynamic Formula
Replies
2
Views
303
Mengli
M

Forum statistics

Threads
1,225,516
Messages
6,185,415
Members
453,289
Latest member
ALPOINT_AIG

Share this page

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
Back
Top