Don't know did these formulas match your request.
I've use many helper columns for slove the request.
Step of calculation. ( No change made to 'CTT 6 LBS Rates' )
1. Declare size of rates in 'CTT 6 LBS Rates'
Y1
** I really want to refer for all rows in 'CTT 6 LBS Rates' and/or want to use INDIRECT
but @Glenn was advise (about INDIRECT side effects) and show me how to refer ranges without using INDIRECT function
by using :INDEX() in next steps. **
2. Find the row in 'CTT 6 LBS Rates' that match [ Sender's City and Destination City ]
Z2
(copy down to end of data area)
3. Set lower limit for each rate in AB1:AI1
and list all rates (refered form row in #2)
AA2
(copy down to end of data area and to column AI)
4. Compare between minimum rates VS rates summary for each weight range.
Q2
Regards.
Bookmarks