Solved Using a table to return lane rate

May 9, 2014 at 09:57:07
Specs: Windows 7
I have a table where going from one point to another will be charged a rate based on the beginning and destination points.
YUM MOR 1.45
YUM SW 1.38
YUM HAR 1.51
SAL IRV 1.4
SAL SF 1.4
SAL MOR 1.7
SAL SW 1.56
SAL HAR 1.78

How do I use a formula in another worksheet to return the lane rate.
So if I have
YUM SW
SAL SF
KC MOR

For example, how do I make it lookup and return the value in column three?


See More: Using a table to return lane rate

Report •

#1
May 9, 2014 at 10:45:03
✔ Best Answer
With your data like this:

     A       B        C
 1) YUM     MOR     1.45
 2) YUM     SW      1.38
 3) YUM     HAR     1.51
 4) SAL     IRV     1.4
 5) SAL     SF      1.4
 6) SAL     MOR     1.7
 7) SAL     SW      1.56
 8) SAL     HAR     1.78
 9)                 
10)                 
11) Start:  SAL     
12) End:    SW      
13) Rate:

In cell B13 enter the formula:

=SUMPRODUCT(($A$1:$A$8=B11)*($B$1:$B$8=B12)*($C$1:$C$8))

You should get 1.56


See how that works for you

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question