Multiple Column Lookup in Excel 2003

[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?

 

[Bernie Deitrick]

=SUMPRODUCT((A2:A1000="City Name")*(B2:B1000 = "Employee Status")*(C2:C1000))

HTH,
Bernie
MS Excel MVP

 

[poor me]

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?

 

[Bernie Deitrick]

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