Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match a value to multple criteria both in rows and columns
In my source data sheet, starting in A1, I have the following information:
Property Dept Total Reported Wk 1 Wk 2 Wk 3 Wk4 ABC Finance 7 1 2 4 0 ABC Sales 2 1 1 0 0 XYX Finance 85 22 26 23 14 I then have one worksheet for each property (property name in A1), with the departments vertically listed, starting in column C: Department Total Reported Wk 1 Wk 2 Wk 3 Wk 4 HR Finance Sales I need for cells D2:H2 to look up the property name from A1, the department from C2 and then fill in the matching values for Total Reported and Wks 1-4. I want it to be blank or display a 0 if there are no corresponding values (i.e. HR has no reports). Is this possible? I'd prefer to automate with a function rather than an advanced filter paste that would require almost as much work as just copying/pasting the data from the data source sheet to the property-specific sheet. Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match a value to multple criteria both in rows and columns
=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))
"GreenDriver" wrote: In my source data sheet, starting in A1, I have the following information: Property Dept Total Reported Wk 1 Wk 2 Wk 3 Wk4 ABC Finance 7 1 2 4 0 ABC Sales 2 1 1 0 0 XYX Finance 85 22 26 23 14 I then have one worksheet for each property (property name in A1), with the departments vertically listed, starting in column C: Department Total Reported Wk 1 Wk 2 Wk 3 Wk 4 HR Finance Sales I need for cells D2:H2 to look up the property name from A1, the department from C2 and then fill in the matching values for Total Reported and Wks 1-4. I want it to be blank or display a 0 if there are no corresponding values (i.e. HR has no reports). Is this possible? I'd prefer to automate with a function rather than an advanced filter paste that would require almost as much work as just copying/pasting the data from the data source sheet to the property-specific sheet. Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match a value to multple criteria both in rows and columns
Assuming that your data sheet is named €śMy Source€ť, then copy this formula to
the report sheet cell D3 and copy it to the adjacent cells as needed. =SUMPRODUCT(('My Source'!$A$1:$A$65000=$A$1)*('My Source'!$B$1:$B$65000=Report!$C3),('My Source'!C$1:C$65000)) Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if two columns match different criteria | Excel Worksheet Functions | |||
match multiple criteria in both rows and columns | Excel Worksheet Functions | |||
How do I Remove Rows that match a criteria | Excel Discussion (Misc queries) | |||
Multple criteria dilemma | Excel Worksheet Functions | |||
How to get number of rows which match criteria | Excel Worksheet Functions |