Aug 22 2019 09:11 AM
Hello everyone,
In one spreadsheet, I have a list of origin cities in column A and a list of destination cities in column B. In column C, I have the mileage between the two.
In another spreadsheet, I have the same list of origin and destination cities but with the rate per mile for each route in column C. This spreadsheet is not in the same order as the first one.
What formulae can I use to bring over the content of spreadsheet 2, column C, and have it in correctly ordered with the content of spreadsheet 1? I am planning on multiplying the rate per mile by the mileage to get the rate for the trip so the mileage and the rates must match.
Thank you guys so much!
Aug 22 2019 11:21 AM
Aug 22 2019 11:32 AM
@jucotransferYou could use SUMIFS to lookup both the origin and destination in the first spreadsheet and return the value for mileage to your second spreadsheet.
Aug 22 2019 02:34 PM
@jucotransfer for such sample
Sheet1:
Sheet2:
rate into first sheet could be returned by
=IFNA(INDEX(Sheet2!C:C, MATCH(1,INDEX( (A2=Sheet2!A:A)*(B2=Sheet2!B:B),0),0)),0)