Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Column Lookup in Excel 2003
I have a four-column table. Column A contains City. Columns B through D
contain Employee Status (Contractor/Full time/Part time). The data in the grid contains the corresponding hourly rates. How can I construct a formula that will find a person's hourly rate based on their City and Employee Status? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Column Lookup in Excel 2003
=SUMPRODUCT((A2:A1000="City Name")*(B2:B1000 = "Employee Status")*(C2:C1000))
HTH, Bernie MS Excel MVP "poor me" <poor wrote in message ... I have a four-column table. Column A contains City. Columns B through D contain Employee Status (Contractor/Full time/Part time). The data in the grid contains the corresponding hourly rates. How can I construct a formula that will find a person's hourly rate based on their City and Employee Status? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Column Lookup in Excel 2003
Thanks, but I should also mention that elsewhere in the spreadsheet are rows
for each person, with one column for that person's City, and next to it a column for that person's Employee Status. How can I adapt your formula to read the values in the person's city and status columns and perform the SUMPRODUCT lookup? "poor me" wrote: I have a four-column table. Column A contains City. Columns B through D contain Employee Status (Contractor/Full time/Part time). The data in the grid contains the corresponding hourly rates. How can I construct a formula that will find a person's hourly rate based on their City and Employee Status? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Column Lookup in Excel 2003
Replace the values with cell references. Let's say that you have the values of interest in columns
H and I, starting in row2: =SUMPRODUCT(($A$2:$A$1000=H2)*($B$2:$B$1000 = I2)*($C$2:$C$1000)) Then copy down. OR use a pivot table - it will give you the same thing, without formulas or requiring a table of values. HTH, Bernie MS Excel MVP "poor me" wrote in message ... Thanks, but I should also mention that elsewhere in the spreadsheet are rows for each person, with one column for that person's City, and next to it a column for that person's Employee Status. How can I adapt your formula to read the values in the person's city and status columns and perform the SUMPRODUCT lookup? "poor me" wrote: I have a four-column table. Column A contains City. Columns B through D contain Employee Status (Contractor/Full time/Part time). The data in the grid contains the corresponding hourly rates. How can I construct a formula that will find a person's hourly rate based on their City and Employee Status? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup all values within multiple columns and copy to new column | Excel Discussion (Misc queries) | |||
Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
multiple column lookup? | Excel Worksheet Functions | |||
Performing a multiple column lookup | Excel Discussion (Misc queries) | |||
Formula to lookup Multiple Column Text and then Count Result | Excel Worksheet Functions |