Help with VLOOKUP & MATCH formula
 

I am looking for help with creating a lookup formula with multiple
conditions. In Sheet1 I have the following table with B2:E2 being the header
row.

B2 C2 D2 E2
B2 Region Type Jan-09 Jan-09
B3 Central Hrs 2 3
B4 Central $'s 9.85 12.78
B5 East Hrs 4 5
B6 East $'s 27.18 65.24
B7 West Hrs 1 1
B8 West $'s 4.48 4.98

On another worksheet (Sheet2), I am looking for a lookup formula to get the
following table. I would like to use a dynamic lookup formula to MATCH the
column month in a range (Sheet1!B2:E2), then lookup ONLY the cell values that
have a "$'s" value in Type column (Col C).

BU Jan-09 Feb-09
Central 9.85 12.78
East 27.18 65.24
West 4.48 4.98

Can this be done by a VLOOKUP with a MATCH function?? Thanks!

Help with VLOOKUP & MATCH formula
 

Correction E2=Feb-09

"GoBucks" wrote:

I am looking for help with creating a lookup formula with multiple
conditions. In Sheet1 I have the following table with B2:E2 being the header
row.

B2 C2 D2 E2
B2 Region Type Jan-09 Feb-09
B3 Central Hrs 2 3
B4 Central $'s 9.85 12.78
B5 East Hrs 4 5
B6 East $'s 27.18 65.24
B7 West Hrs 1 1
B8 West $'s 4.48 4.98

On another worksheet (Sheet2), I am looking for a lookup formula to get the
following table. I would like to use a dynamic lookup formula to MATCH the
column month in a range (Sheet1!B2:E2), then lookup ONLY the cell values that
have a "$'s" value in Type column (Col C).

BU Jan-09 Feb-09
Central 9.85 12.78
East 27.18 65.24
West 4.48 4.98

Can this be done by a VLOOKUP with a MATCH function?? Thanks!

Help with VLOOKUP & MATCH formula
 

In article ,
GoBucks wrote:

I am looking for help with creating a lookup formula with multiple
conditions. In Sheet1 I have the following table with B2:E2 being the header
row.

B2 C2 D2 E2
B2 Region Type Jan-09 Jan-09
B3 Central Hrs 2 3
B4 Central $'s 9.85 12.78
B5 East Hrs 4 5
B6 East $'s 27.18 65.24
B7 West Hrs 1 1
B8 West $'s 4.48 4.98

On another worksheet (Sheet2), I am looking for a lookup formula to get the
following table. I would like to use a dynamic lookup formula to MATCH the
column month in a range (Sheet1!B2:E2), then lookup ONLY the cell values that
have a "$'s" value in Type column (Col C).

BU Jan-09 Feb-09
Central 9.85 12.78
East 27.18 65.24
West 4.48 4.98

Can this be done by a VLOOKUP with a MATCH function?? Thanks!

Assuming that on Sheet2, A2:A4 contains Central, East, and West, and
B1:C1 contains Jan-09 and Feb-09, try...

B2, copied across and down:

=SUMPRODUCT(--(Sheet1!$B$3:$B$8=$A2),--(Sheet1!$C$3:$C$8="$'s"),INDEX(She
et1!$D$3:$E$8,0,MATCH(B$1,Sheet1!$D$2:$E$2,0)))

--
Domenic
http://www.xl-central.com

Help with VLOOKUP & MATCH formula
 

I recommend you use a PIVOT table for this. If you preferred the formula then
try the following.

Sheet 2:
In B2:
=SUMPRODUCT((Sheet1!$B$3:$B$8=$A2)*(Sheet1!$C$3:$C $8="$'s"),Sheet1!D$3:D$8)

copy across and down



"GoBucks" wrote:

I am looking for help with creating a lookup formula with multiple
conditions. In Sheet1 I have the following table with B2:E2 being the header
row.

B2 C2 D2 E2
B2 Region Type Jan-09 Jan-09
B3 Central Hrs 2 3
B4 Central $'s 9.85 12.78
B5 East Hrs 4 5
B6 East $'s 27.18 65.24
B7 West Hrs 1 1
B8 West $'s 4.48 4.98

On another worksheet (Sheet2), I am looking for a lookup formula to get the
following table. I would like to use a dynamic lookup formula to MATCH the
column month in a range (Sheet1!B2:E2), then lookup ONLY the cell values that
have a "$'s" value in Type column (Col C).

BU Jan-09 Feb-09
Central 9.85 12.78
East 27.18 65.24
West 4.48 4.98

Can this be done by a VLOOKUP with a MATCH function?? Thanks!

Help with VLOOKUP & MATCH formula
 

Thank you very much! This worked like a charm.

"Domenic" wrote:

In article ,
GoBucks wrote:

I am looking for help with creating a lookup formula with multiple
conditions. In Sheet1 I have the following table with B2:E2 being the header
row.

B2 C2 D2 E2
B2 Region Type Jan-09 Jan-09
B3 Central Hrs 2 3
B4 Central $'s 9.85 12.78
B5 East Hrs 4 5
B6 East $'s 27.18 65.24
B7 West Hrs 1 1
B8 West $'s 4.48 4.98

On another worksheet (Sheet2), I am looking for a lookup formula to get the
following table. I would like to use a dynamic lookup formula to MATCH the
column month in a range (Sheet1!B2:E2), then lookup ONLY the cell values that
have a "$'s" value in Type column (Col C).

BU Jan-09 Feb-09
Central 9.85 12.78
East 27.18 65.24
West 4.48 4.98

Can this be done by a VLOOKUP with a MATCH function?? Thanks!

Assuming that on Sheet2, A2:A4 contains Central, East, and West, and
B1:C1 contains Jan-09 and Feb-09, try...

B2, copied across and down:

=SUMPRODUCT(--(Sheet1!$B$3:$B$8=$A2),--(Sheet1!$C$3:$C$8="$'s"),INDEX(She
et1!$D$3:$E$8,0,MATCH(B$1,Sheet1!$D$2:$E$2,0)))

--
Domenic
http://www.xl-central.com

Help with VLOOKUP & MATCH formula
 

Thank you TM!!!

"Teethless mama" wrote:

I recommend you use a PIVOT table for this. If you preferred the formula then
try the following.

Sheet 2:
In B2:
=SUMPRODUCT((Sheet1!$B$3:$B$8=$A2)*(Sheet1!$C$3:$C $8="$'s"),Sheet1!D$3:D$8)

copy across and down



"GoBucks" wrote:

I am looking for help with creating a lookup formula with multiple
conditions. In Sheet1 I have the following table with B2:E2 being the header
row.

B2 C2 D2 E2
B2 Region Type Jan-09 Jan-09
B3 Central Hrs 2 3
B4 Central $'s 9.85 12.78
B5 East Hrs 4 5
B6 East $'s 27.18 65.24
B7 West Hrs 1 1
B8 West $'s 4.48 4.98

On another worksheet (Sheet2), I am looking for a lookup formula to get the
following table. I would like to use a dynamic lookup formula to MATCH the
column month in a range (Sheet1!B2:E2), then lookup ONLY the cell values that
have a "$'s" value in Type column (Col C).

BU Jan-09 Feb-09
Central 9.85 12.78
East 27.18 65.24
West 4.48 4.98

Can this be done by a VLOOKUP with a MATCH function?? Thanks!