Help with VLOOKUP & MATCH formula

  • Thread starter Thread starter GoBucks
  • Start date Start date
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!
 
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!
Click to expand...

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:D$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)))
Click to expand...
 
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:D$8)

copy across and down
Click to expand...
 
Back
Top