Looking up multiple values on

Question

I'm lookng for a function that can look up 3 values across columns (the date, location, and location activity) and return a corresponding value (distance).

Please help.

Answer

if it is a unique combination then

Values being sought

M1: date N1: location O1: location activity

lookup in Column A: date Column B: Location Column C: Location Activity Column D: distance

data in rows 2 to 5000

=sumproduct(--($A$2:$A$500=$M$1),--($B$2:$B$5000=$N$1),--($C$2:$C$5000=$O$1),$D$2:$D$5000)

if you have2007 or later then

=Sumifs($D$2:$D$5000,$A$2:$A$500,$M$1,$B$2:$B$5000,$N$1,$C$2:$C$5000,$O$1)

would be faster