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?

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?

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?

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?