Help Excel Formula - lookup with 3 criteria?

[coliemom]

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.

 

[Weazel]

maybe...

Excel 2010

	A	B	C	D	E	F

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

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Jane[/TD]
[TD="bgcolor: #FAFAFA"]North Shore[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/15/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA"]Ian[/TD]
[TD="bgcolor: #FAFAFA"]Aberdeen[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0.5[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Lewis[/TD]
[TD="bgcolor: #FAFAFA"]Aberdeen[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0.25[/TD]

[TD="align: center"]5[/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"]6[/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"]7[/TD]
[TD="bgcolor: #D9D9D9"]Date[/TD]
[TD="bgcolor: #D9D9D9"]Associate[/TD]
[TD="bgcolor: #D9D9D9"]Area[/TD]
[TD="bgcolor: #D9D9D9"]Promises[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/15/2014[/TD]
[TD="bgcolor: #FAFAFA"]Ian[/TD]
[TD="bgcolor: #FAFAFA"]Aberdeen[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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"]D8[/TH]
[TD="align: left"]{=VLOOKUP(A8&B8&C8,CHOOSE({1,2},A2:A4&C2:C4&D2:D4,E2:E4),2,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Teeroy]

You could also use a Pivot Table for this and extract the information using Getpivotdata e.g.

=GETPIVOTDATA("Promises",$A$1,"Date","01/15/14","Associate","Ian","Area","Aberdeen")

 

[Yesim]

After seeing this, I tried using it in my VBA code and wrote this

Sheets("Data").Select

noRows = Cells(Rows.Count, "A").End(xlUp).Row
M = Sheets("1").Range("E2").Value
P = Sheets("1").Range("B2").Value
V = Sheets("1").Range("D2").Value
Y = Sheets("1").Range("W2").Value

Lookingfor = vlookupVBA(M&P&V&Y, Choose({1,2},A2:A&noRows&B2:B&noRows&D2:D&noRows&G2:G&noRows&K2:K&noRows), 2,0)

But I got an error at P& saying "Expected list separator or )" . I then tried the following

Lookingfor = vlookupVBA(Sheets("1").Range("E2")&Sheets("1").Range("B2")&Sheets("1").Range("D2")&Sheets("1").Range("W2"),Choose({1,2},A2:A&noRows&B2:B&noRows&D2:D&noRows&G2:G&noRows&K2:K&noRows), 2,0)

and got a new compiler error at Choose({1,... saying { is "Invalid Character" I tried to figure out what {1,2} is doing, but alas I couldn't. What is it used for?

BTW, I use the following VBA procedure for vlookupVBA (thanks to another user who posted it)

Function vlookupVBA(lookupValue, rangeString, colOffset)

vlookupVBA = "#N/A"
On Error Resume Next
Dim table_lookup As Range

Set table_lookup = Range(rangeString)
vlookupVBA = Application.WorksheetFunction.VLookup(lookupValue, table_lookup, colOffset, False)

End Function 'vlookupVBA

Suggestions?

 

[Weazel]

sorry, I don't really know much about VBA

you could try...

=INDEX(E2:E4,MATCH(A7&B7&C7,A2:A4&C2:C4&D2:D4,0)) control shift enter

which gets rid of the need for the array syntax in the choose

 

[coliemom]

Thanks Weasel. I got this to work. The issue I'm having is if it's not meeting criteria, it's coming up #N/A. If 'm totaling the promises or the hrs the =SUM will not work because of the #N/A. Is there one more thing I can add to this formula so that its coming u 0 instead of #N/A if it doesn't meet criteria?

 

[Yesim]

sorry, I don't really know much about VBA

you could try...

=INDEX(E2:E4,MATCH(A7&B7&C7,A2:A4&C2:C4&D2:D4,0)) control shift enter

which gets rid of the need for the array syntax in the choose

Thanks, for your reply, I will work on it. However, I am still wondering what {1,2} is used for, any comments?

 

[Weazel]

the {1,2} is telling the choose to return the 2 values together to the vlookup which becomes the lookup array

essentially...

=VLOOKUP(A8&B8&C8,{"41640JaneNorth Shore",6;"41654IanAberdeen",5;"41640LewisAberdeen",2},2,0) which now matches the concatenated lookup values in A,B and C, the column index would then be the value you are trying to return in the second column created by the choose.

hopefully that makes sense

 

[Yesim]

the {1,2} is telling the choose to return the 2 values together to the vlookup which becomes the lookup array

essentially...

=VLOOKUP(A8&B8&C8,{"41640JaneNorth Shore",6;"41654IanAberdeen",5;"41640LewisAberdeen",2},2,0) which now matches the concatenated lookup values in A,B and C, the column index would then be the value you are trying to return in the second column created by the choose.

hopefully that makes sense

Oh, I see! Thanks!

 

[coliemom]

maybe...

Excel 2010

	A	B	C	D	E	F

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

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Jane[/TD]
[TD="bgcolor: #FAFAFA"]North Shore[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/15/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA"]Ian[/TD]
[TD="bgcolor: #FAFAFA"]Aberdeen[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0.5[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Lewis[/TD]
[TD="bgcolor: #FAFAFA"]Aberdeen[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0.25[/TD]

[TD="align: center"]5[/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"]6[/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"]7[/TD]
[TD="bgcolor: #D9D9D9"]Date[/TD]
[TD="bgcolor: #D9D9D9"]Associate[/TD]
[TD="bgcolor: #D9D9D9"]Area[/TD]
[TD="bgcolor: #D9D9D9"]Promises[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/15/2014[/TD]
[TD="bgcolor: #FAFAFA"]Ian[/TD]
[TD="bgcolor: #FAFAFA"]Aberdeen[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]{=VLOOKUP(A8&B8&C8,CHOOSE({1,2},A2:A4&C2:C4&D2:D4,E2:E4),2,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

I have used this array formula from Weazel but I need it to add up multiple entries from the source sheet that match the criteria. I also need to total results. The above formula results in #N/A if all the criteria isn't met, which means you can't use something like a =SUM to total results. How would you update this formula to meet the additional requirements?