vlookup

[vishy]

hi,
i m workin on a application using vba developer...am not used to vb.
thing is i hav data consisting 3 columns..
1. source- area name
2. destination-area name
3.kms- numeric value

each data unique combination...but repeated places..
eg: califrnia to new york is a combination, so does california to texas, texas to new yok...
the data goes hundreds..

wat the application consists is ..two combo box..
one for source nd another for destin...i populated the list by usin properties- list fill range and linked it to two different cells...

the requirement is..

excel has to lookup the value in the two different linked cell of combo box ..ant return kms value...

excel has to match the combination to data and return third column value

i tried but get nly #NA...

pls help me on this...suggest some less complicated solution if any

 

[moonfish]

Just to get this straight...

-Your table looks like this:
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:79px;" /><col style="width:79px;" /><col style="width:35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffcc00; font-weight:bold; ">start</td><td style="background-color:#ffcc00; font-weight:bold; ">finish</td><td style="background-color:#ffcc00; font-weight:bold; ">km</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Amsterdam</td><td >Berlin</td><td style="text-align:right; ">648</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Amsterdam</td><td >Madrid</td><td style="text-align:right; ">1782</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Amsterdam</td><td >Paris</td><td style="text-align:right; ">514</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Berlin</td><td >Madrid</td><td style="text-align:right; ">2527</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Berlin</td><td >Paris</td><td style="text-align:right; ">1094</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Madrid</td><td >Paris</td><td style="text-align:right; ">1268</td></tr></table>
-You want to find the distance based on start and end points.
-Each combination is unique, so Paris-Berlin is the same as Berlin-Paris and only one of those is in your table.

 

[vishy]

exactly....

 

[moonfish]

This should work for you:

Excel Workbook
	B	C	D
1	start	finish	km
2	Amsterdam	Berlin	648
3	Amsterdam	Madrid	1782
4	Amsterdam	Paris	514
5	Berlin	Madrid	2527
6	Berlin	Paris	1094
7	Madrid	Paris	1268
8	*	*	*
9	Paris	Amsterdam	514
Sheet

 

[vishy]

workin..great..


wil come back wit more query