Returning the value of the Non-Expired Rate

[MnMCarta]

I have a tariff guide that has different Effective and Expired rates for lanes that I am trying to compare to what was tendered (rate given to carrier for the load to deliver) and some of the loads fall under 1 rate due to the date they shipped and others fall under another rate as the original rate was expired and a new rate was applied. Attached is a generic example and below is the formula I have started with (getting no results as I know I'm thinking through this too hard)

IF(C325<>"Base Rate","Not Base",IF(ISNA(VLOOKUP(AD325,'Payable Tariff Upload'!AJ:AL,3,FALSE)),"No Tariff",INDEX('Payable Tariff Upload'!AL:AL,MATCH(IF('Load Charges'!AA325>='Payable Tariff Upload'!AD:AD,'Payable Tariff Upload'!AL:AL,0),""))))

How can I return the correct value for the line that falls in the right date range?

[Mazzaropi]

MnMCarta, Good evening.

Try to use:

=SUMPRODUCT(($A$2:$A$3=B10)*($B$2:$B$3<=C10)*($C$2:$C$3>=C10)*($D$2:$D$3))

Date Range Value Return-OK.xlsx

Is that what you're looking for?
I hope it helps.

[MnMCarta]

Whoa.... that's amazing!!! I'm trying to understand it but for now it's working!! Thank you soooo much You are my savior!

[Mazzaropi]

MnMCarta, Good evening.

Clarifying your doubts.

The asterisk (*) serves to link conditions and replaces the signals (;--) of SUMPRODUCT function.

Your formula is correct.
Delete only the last parenthesis.

Looks like this:

=SUMPRODUCT(('Payable Tariff Upload'!AJ2:AJ25628='Load Charges'!AD2)*('Payable Tariff Upload'!AD2:AD25628<='Load Charges'!AA2)*('Payable Tariff Upload'!AE2:AE25628>='Load Charges'!AA2)*('Payable Tariff Upload'!AL2:AL25628))

This formula without the asterisk would look like this:

=SUMPRODUCT(--('Payable Tariff Upload'!AJ2:AJ25628='Load Charges'!AD2);--('Payable Tariff Upload'!AD2:AD25628<='Load Charges'!AA2);--('Payable Tariff Upload'!AE2:AE25628>='Load Charges'!AA2);--('Payable Tariff Upload'!AL2:AL25628))


What kind of error is appearing?

[MnMCarta]

MnMCarta, Good evening.

Clarifying your doubts.

The asterisk (*) serves to link conditions and replaces the signals (;--) of SUMPRODUCT function.

Your formula is correct.
Delete only the last parenthesis.

Looks like this:

=SUMPRODUCT(('Payable Tariff Upload'!AJ2:AJ25628='Load Charges'!AD2)*('Payable Tariff Upload'!AD2:AD25628<='Load Charges'!AA2)*('Payable Tariff Upload'!AE2:AE25628>='Load Charges'!AA2)*('Payable Tariff Upload'!AL2:AL25628))

This formula without the asterisk would look like this:

=SUMPRODUCT(--('Payable Tariff Upload'!AJ2:AJ25628='Load Charges'!AD2);--('Payable Tariff Upload'!AD2:AD25628<='Load Charges'!AA2);--('Payable Tariff Upload'!AE2:AE25628>='Load Charges'!AA2);--('Payable Tariff Upload'!AL2:AL25628))


What kind of error is appearing?

It was a value error when i was using the entire column. When i changed it to the rows 2-25628 it worked. I just didnt want to have to go in each time and adjust it.

[Mazzaropi]

MnMCarta, Good morning.

This is possibly because there is in the AL column any cell containing text or a blank (space).

It could be, for example, the column header.

Since this column will be used as totalizer values it causes this error.

Take a look and see if, by chance, it is happening.

Please, tell us if it worked for you.
I hope it helps.