Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Vlookup / Match Formula across 8 workbooks | Excel Worksheet Functions | |||
Index & match/IF/Vlookup formula help | Excel Discussion (Misc queries) | |||
If isna match vlookup formula | Excel Worksheet Functions | |||
n/a in vlookup/index/match formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |