|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 16th, 2009, 07:54 PM
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
April 16th, 2009, 07:58 PM
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
April 16th, 2009, 08:08 PM
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
April 16th, 2009, 08:17 PM
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  $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
April 16th, 2009, 08:30 PM
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
April 16th, 2009, 08:31 PM
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 $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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
