Multiple Column Lookup in Excel 2003

  • Thread starter Thread starter poor me
  • Start date Start date
P

poor me

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?
 
=SUMPRODUCT((A2:A1000="City Name")*(B2:B1000 = "Employee Status")*(C2:C1000))

HTH,
Bernie
MS Excel MVP
 
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?
 
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
 
Back
Top