Formula Lookup Multiple Distance

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Nov 30, 2015

• #2

Excel 2010
	A	B	C	D	E	F
1	Origin
2		A	B	C	D	E
3	A	0	50	100	200	400
4	B	50	0	50	100	200
5	C	100	50	0	100	100
6	D	200	100	100	0	50
7	E	400	200	100	50	0
8
9
10
11
12	Origin	A
13
14	Destination	Distance
15	B	50
16	C	100
17	D	200
18	E	400
Sheet1

Cell Formulas
Range	Formula
B15	=INDEX($B$3:$F$7,MATCH($B$12,$A$3:$A$7,0),MATCH($A15,$B$2:$F$2,0))

 

Upvote 0

D

decent_boy

Board Regular

Joined

Dec 5, 2014

Messages

130

Office Version

1. 2016

Platform

1. Windows

• Nov 30, 2015

• #3

Comfy thanks for your reply, but there is confusion here because you keep only Origin but i have to select origin and destination both for example if select origin e and destination a then what will happen ?

 

Upvote 0

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Nov 30, 2015

• #4

If you select origin e and destination a it will return 400 as per your table.

 

Upvote 0

D

decent_boy

Board Regular

Joined

Dec 5, 2014

Messages

130

Office Version

1. 2016

Platform

1. Windows

• Nov 30, 2015

• #5

If I select origin e and destination a then table looks like (E to A transportation movement)

Area	KM
D	50
C	100
B	200
A	400

<tbody>
</tbody>

 

Upvote 0

D

decent_boy

Board Regular

Joined

Dec 5, 2014

Messages

130

Office Version

1. 2016

Platform

1. Windows

• Nov 30, 2015

• #6

Can anybody solve it please ?

 

Upvote 0

D

decent_boy

Board Regular

Joined

Dec 5, 2014

Messages

130

Office Version

1. 2016

Platform

1. Windows

• Nov 30, 2015

• #7

How can it be solved ? please tell a way.

 

Upvote 0

D

decent_boy

Board Regular

Joined

Dec 5, 2014

Messages

130

Office Version

1. 2016

Platform

1. Windows

• Nov 30, 2015

• #8

if it can not be solved through formula , can it be solved through VB or any other solution

 

Upvote 0

M

MickG

MrExcel MVP

Joined

Jan 9, 2008

Messages

14,841

• Nov 30, 2015

• #9

Based on "Comfy's" layout, try this UDF
After pasteing in Basic module , use in "B15" down as :- =Kg(A15)

Code:

Function Kg(Des As Range) As Double
Dim RngAc As Range, RngRw As Range, Dic As Object, oGin As Range
Dim Rw As Range, Ac As Range
Application.Volatile
Set oGin = Range("B12")
Set RngAc = Range("B2:F2"): Set RngRw = Range("A3:A7")
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each Rw In RngRw
    For Each Ac In RngAc
      Dic(Rw.Value & "," & Ac.Value) = Cells(Rw.Row, Ac.Column)
    Next Ac
Next Rw
Kg = Dic(oGin & "," & Des)
End Function

 

Upvote 0

H

Hercules1946

Well-known Member

Joined

Oct 6, 2007

Messages

545

• Nov 30, 2015

• #10

decent_boy said:

How can it be solved ? please tell a way.

Click to expand...

The correct result is returned for origin E and destination A provided you have entered the right arrangement of letters in B12, and A15 to A18 (as your table in #5).

Does that work for you?

 

Upvote 0