Result based on criteria fro 2 lists

[marcidee]

Please can someone advise me the best way to go around this scenario. To help ease calculations for a mileage report I want to put together.

I am thinking I will have 2 lists that generate a result (you maybe able to advise a better way?) – a list of clients (going from) and a list of clients (going to) – someone will have worked out what that mileage is and enter that in ‘result’ (behind the scenes)

Drop down lists - select:
Client a Client b result = 5 miles
Client a client c result = 7 miles

The user will then have two drop downs 1) client from and 2) client to - then the result will appear.

Thank you

Marc

 

[DanteAmor]

We give an example.
If you increase the information you must modify the ranges in the formulas

This is another example:

=SUMPRODUCT(((Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$100=B2)+(Sheet2!$A$2:$A$100=B2)*(Sheet2!$B$2:$B$100=A2))*(Sheet2!$C$2:$C$100) )

You must change 100 for the last row with data from sheet2

 

[marcidee]

Perfect - thank you

 

[DanteAmor]

Perfect - thank you

I'm glad to help you. Thanks for the feedback.