Please I want to write a formula to get the Distance (4th column) between From and To Cities (2nd and 3rd columns) for a particular Order ID (1st column) in Table 1 while looking up data from the Distances worksheet as shown below.

Distances worksheet:

CodePudding user response：

So a quick version:

INDEX(A2:D5,MATCH(B8,A2:A5,0),MATCH(B9,A2:D2,0))

B8 is the starting point, B9 end. Did not repeat all your data as you gave it as a picture and I am too lazy to type all of that.

CodePudding user response：

You can do it easily with AVERAGEIFS:

Formula in cell H4:

=IF(H$3=$G4;"";AVERAGEIFS($D$2:$D$19;$B$2:$B$19;$G4;$C$2:$C$19;H$3))

Drag to left and drag to bottom.

You need to use AVERAGEIFS because distance between points is the same in both directions. Because in your data yo got, as example, Melbourner - Sidney in OrderId 2 and got Sidney - Melbourne in OrderId 4. It's the same distance duplicated so if you just sum, you will create an error.

CodePudding user response：

Using XLOOKUP() Function

Formula used in cell D2 --> Applicable To Excel 2021 & O365 Users Only

=XLOOKUP(B2,$F$3:$F$7,XLOOKUP(C2,$G$2:$K$2,$G$3:$K$7),"Not Found")

So, the second XLOOKUP() Function returns

XLOOKUP(C2,$G$2:$K$2,$G$3:$K$7)

Returns the Distance (km) that takes from any of the places listed in Column B to reach Canberra, since I have taken the first cell as an example so its showing for the Canberra, this goes same for the other Destinations in Column C

{285;676;1199;3735;0}

Lastly wrapping this one within another XLOOKUP() Function where the lookup value is from the Column B shall return the exact distance required.

3 Alternative formulas:

• For All Users

=VLOOKUP(B2,$F$3:$K$7,MATCH(C2,$F$2:$K$2,0),0)

• For Office 365 & Excel 2021

=FILTER(FILTER($G$3:$K$7,(B2=$F$3:$F$7)),C2=$G$2:$K$2)

• For Office 365 & Excel 2021

=INDEX(FILTER($G$3:$K$7,B2=$F$3:$F$7),XMATCH(C2,$G$2:$K$2,0))

Note: For convenience I have shown both the tables in same worksheet, hence you may need to change the ranges accordingly as per your database.