Mileage reimbursement Help

Mtmoroni

I have automated an expense report. This worked great as I did an index/match on the date the expense was for, compared to the year, and reported back the mileage reimbursement rate.

=IF(ISBLANK(Miles@row), Miles@row * 0, Miles@row * (INDEX({Mileage Reimbursement master Range 1}, MATCH([F*Year]@row, {Mileage Reimbursement master Range 2}, 0))))

The core sheet looked like the below (minus the start and end, that i just added)

My goal is to get something that looks at the expense sheet of DATE, if date is between the start and end date, it will pull back the rate into the calculation.

I'm just not sure how to do a formula without nested if statements.

Can anyone help out?

Paul Newcome

Your MATCH would look more like this...

MATCH(MAX(COLLECT({Start Date}, {Start Date}, @cell <= Date@row)), {Start Date}, 0)

Paul Newcome

Your MATCH would look more like this...

MATCH(MAX(COLLECT({Start Date}, {Start Date}, @cell <= Date@row)), {Start Date}, 0)

Mtmoroni

@Paul Newcome Thank you!! This yielded the exact results I needed.

Below is the exact formula I use for our expense report.

=IF(ISBLANK(Miles@row), Miles@row * 0, Miles@row * (INDEX({Mileage Reimbursement Rate}, MATCH(MAX(COLLECT({Mileage Reimbursement Start Date}, {Mileage Reimbursement Start Date}, @cell <= Date@row)), {Mileage Reimbursement Start Date}, 0)))

Paul Newcome

Happy to help. 👍️