# Help with VLOOKUP & MATCH formula

G

#### GoBucks

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!

Correction E2=Feb-09

GoBucks said:
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)))

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\$8)

copy across and down

Thank you very much! This worked like a charm.

Domenic said:
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)))

Thank you TM!!!

Teethless mama said:
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\$8)

copy across and down