Need Help with a Look up formula.

[Jithe]

Hi Guys,

I need some help regarding some work related calculations. I need a formula or a methodology.

Assume there are 2 tables

Table 1

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]ID No[/TD]
[TD="align: center"]From[/TD]
[TD="align: center"]To[/TD]
[TD="align: center"]Distance KM[/TD]
[/TR]
[TR]
[TD="align: center"]1st Jan[/TD]
[TD="align: center"]12546[/TD]
[TD="align: center"]Point A[/TD]
[TD="align: center"]Point B[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2nd Jan[/TD]
[TD="align: center"]15976[/TD]
[TD="align: center"]Point B[/TD]
[TD="align: center"]Point C[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD="align: center"]3rd Jan[/TD]
[TD="align: center"]14753[/TD]
[TD="align: center"]Point A[/TD]
[TD="align: center"]Point C[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD="align: center"]4th Jan[/TD]
[TD="align: center"]15853[/TD]
[TD="align: center"]Point C[/TD]
[TD="align: center"]Point D[/TD]
[TD="align: center"]?[/TD]
[/TR]
</tbody>[/TABLE]




Table 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]KM[/TD]
[/TR]
[TR]
[TD]Point A[/TD]
[TD]Point B[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD]Point A[/TD]
[TD]Point C[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD]Point B[/TD]
[TD]Point C[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD]Point C[/TD]
[TD]Point D[/TD]
[TD="align: center"]13[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula for when I type the From and To locations in Table 1, it looks up the value corresponding KM in Table 2 and places the KM value in table 1.


Your help is greatly appreciated.

Thank You
Regards,
Jithe

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

1​

[/td][td]

Date​

[/td][td]

ID No​

[/td][td]

From​

[/td][td]

To​

[/td][td]

Distance KM​

[/td][td][/td][td][/td][td]From[/td][td]To[/td][td]KM[/td][/tr]
[tr][td]

2​

[/td][td]

1st Jan​

[/td][td]

12546​

[/td][td]

Point A​

[/td][td]

Point B​

[/td][td]

5​

[/td][td][/td][td][/td][td]Point A[/td][td]Point B[/td][td]

5​

[/td][/tr]
[tr][td]

3​

[/td][td]

2nd Jan​

[/td][td]

15976​

[/td][td]

Point B​

[/td][td]

Point C​

[/td][td]

9​

[/td][td][/td][td][/td][td]Point A[/td][td]Point C[/td][td]

7​

[/td][/tr]
[tr][td]

4​

[/td][td]

3rd Jan​

[/td][td]

14753​

[/td][td]

Point A​

[/td][td]

Point C​

[/td][td]

7​

[/td][td][/td][td][/td][td]Point B[/td][td]Point C[/td][td]

9​

[/td][/tr]
[tr][td]

5​

[/td][td]

4th Jan​

[/td][td]

15853​

[/td][td]

Point C​

[/td][td]

Point D​

[/td][td]

13​

[/td][td][/td][td][/td][td]Point C[/td][td]Point D[/td][td]

13​

[/td][/tr]
[/table]

In E2 control+shift+enter, not just enter, and copy down:

=INDEX($J$2:$J$5,MATCH(D2,IF($H$2:$H$5=C2,$I$2:$I$5),0))

 

[Jithe]

Dear Aladin Akyurek

Thank you so much this formula really solved my problem and made the calculations much more easier.

Regards,
Jithe

 

[Aladin Akyurek]

Dear Aladin Akyurek

Thank you so much this formula really solved my problem and made the calculations much more easier.

Regards,
Jithe

You are welcome. Thanks for the update.