CODEWITHSUNDEEP
excel
Bigste
Bigste

Reputation: 1

Set mileage destination displaying

I have some experience with excel but its very limited. I have a list of regular To & From destinations setup as historical data along with the mileage between.

What I have is basically cell A1 WIGAN + cell B1 LUTTERWORTH in Sheet 2 and I want a formula to match both of these from Sheet 1 and display the mileage. If the journey has no history then to display a NIL value.

It sounds simple but as yet I've not located anything that works or been able to work it out myself.

enter image description here

Upvotes: 0

Views: 49

Answers (2)

user4039065
user4039065

Reputation:

You could minimize the cross-reference table of mileage values by adding a reversed lookup if the first fails.

    aggregate_mileage

In Sheet5!C2 as,

=INDEX(Mileage!C:C, IFERROR(AGGREGATE(15, 6, ROW($1:$99)/((Mileage!$A$1:$A$99=A2)*(Mileage!$B$1:$B$99=B2)), 1), AGGREGATE(15, 6, ROW($1:$99)/((Mileage!$A$1:$A$99=B2)*(Mileage!$B$1:$B$99=A2)), 1)))

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Try this:

=INDEX('Sheet1'!C:C,MATCH("NEWHOUSE",IF('Sheet1'!A:A = "WIGAN",'Sheet1'!B:B),0))

It is an array formula and must be confirmed with Ctrl-Shift-Enter.

Change the Column references to the actual reference; change C:C to $C$2:$C$1000. It will speed it up.

Upvotes: 1

Related Questions