# Result based on criteria fro 2 lists

#### marcidee

##### Board Regular
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

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Special-K99

##### Well-known Member
So 3 clients labelled A, B and C

Create a table consisting of 3 columns
Client From, Client To, and Distance

Then just do a VLOOKUP()/INDEX(MATCH(...)) based on the From and To
So

Code:
``````A   B   5miles
A   C   7miles
B   C   10miles``````
If you place the Client From/ To in alphabetical order (irrespective of if the from/to) you can do away with duplicate rows in the table since the distance from A to B would be the same as B. to A. For example A B C is 6 combinations AtoB AtoC BtoC BtoA BtoC CtoA. but the last 3 of these are the same as the first 3 for distance so you could do away with the last 3 rows of the table and just place the Clients in alpahabetical order, ie for BtoA just change it to AtoB and lookup AtoB.

Last edited:

#### marcidee

##### Board Regular
Thank you for your reply - based on you advice above - can Client A and Client B be indifferent columns because if the list is long it will be mush easier for them to choose

#### Special-K99

##### Well-known Member
Yes. Client A and B in different columns with a mileage, hence the 3 columns
Im just saying why have this

A to B 5 miles
A to C 7 miles
B to C 10 miles
B to A 5 miles (we know this already from above)
C to A 7 miles (we know this already from above)
C to B 10 miles (we know this already from above)

when you just need this

A to B 5 miles
A to C 7 miles
B to C 10 miles

You can just set a formula to say if its "B to A" in the formula just check if "From" is greater than "To" just swap them around and you only need to lookup 3 rows instead of 6, which if your saying the list of clients is long you can halve the length of the list as displayed int he tables above. No need to duplicate distances that have already been entered.

#### marcidee

##### Board Regular
thank you that's great - if the data is in table 1 on sheet 2 columns A,B, C from row - any chance you could give me the exact formula =VLOOKUP()/INDEX(MATCH(...))?

#### Special-K99

##### Well-known Member
I might have overdone this but try

in Sheet2!A1:C3 put

Code:
``````A B 5
A C 7
B C 10``````
in Sheet1!C1 put
=INDEX(Sheet2!C\$1:\$C\$3,AGGREGATE(15,6,ROW(\$A\$1:\$C\$3)/(((IF(B1 < A1,B1,A1)=Sheet2!A\$1:A\$3)*(IF(A1 < B1,B1,A1)=Sheet2!B\$1:B\$3))),ROWS(A\$1:A1))-(1-1),1)

Put A in Sheet1!A1
Put B in Sheet1!B1
Result is 5

Now Swap A1 and B1 around, result is still 5 so saves you having to duplicate distances in the table, so you only need to put AtoB, not BtoA as well.
The highlighted part of the formula swaps the Client names round if necessary.

Last edited:

#### marcidee

##### Board Regular
Thank you for this - This works really well - however it only appears to work on the first 2 rows of the table in sheet 2?

#### marcidee

##### Board Regular
Also if I drag the drop downs and formula down on sheet 1 - if only works on row 1

#### DanteAmor

##### Well-known Member
Try the following formulas
You can use sumproduct or sumifs

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:100.75px;" /><col style="width:102.65px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Client From</td><td >Client To</td><td >Mileage</td><td >Mileage</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td >B</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A</td><td >C</td><td style="text-align:right; ">7</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >C</td><td >A</td><td style="text-align:right; ">7</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >B</td><td >A</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >B</td><td >C</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=SUMPRODUCT(((Sheet2!\$A\$2:\$A\$4=A2)*(Sheet2!\$B\$2:\$B\$4=B2)+(Sheet2!\$A\$2:\$A\$4=B2)*(Sheet2!\$B\$2:\$B\$4=A2))*(Sheet2!\$C\$2:\$C\$4))</td></tr><tr><td >D2</td><td >=SUMIFS(Sheet2!\$C\$2:\$C\$4,Sheet2!\$A\$2:\$A\$4,A2,Sheet2!\$B\$2:\$B\$4,B2)+SUMIFS(Sheet2!\$C\$2:\$C\$4,Sheet2!\$A\$2:\$A\$4,B2,Sheet2!\$B\$2:\$B\$4,A2)</td></tr></table></td></tr></table>

Sheet2
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:95.05px;" /><col style="width:89.35px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Client From</td><td >Client To</td><td >Miles</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td >B</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A</td><td >C</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >B</td><td >C</td><td style="text-align:right; ">10</td></tr></table>

#### marcidee

##### Board Regular
Thank you for this - SUMProduct works but not SUMIF - however it does not pick up Row 5 or after of the table in sheet2 - (so it works for the first 3 rows of information)

1,102,202
Messages
5,485,319
Members
407,496
Latest member
PttrsnMrgn

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...