what lookup formula

[mond]

A B C D
<TABLE style="MARGIN: auto auto auto 38.25pt; WIDTH: 160.6pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=214><TBODY><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 21.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=29 noWrap>

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-top-alt: solid windowtext .5pt" width=62 noWrap>

0 °<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-top-alt: solid windowtext .5pt" width=62 noWrap>

1 °<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-top-alt: solid windowtext .5pt" width=62 noWrap>

2 °<o></o>​

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 21.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-left-alt: solid windowtext .5pt" width=29 noWrap>

0’<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

0.00<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

59.60<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

119.22<o></o>​

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 21.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-left-alt: solid windowtext .5pt" width=29 noWrap>

1’ <o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

0.99<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

60.59<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

120.22<o></o>​

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 21.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-left-alt: solid windowtext .5pt" width=29 noWrap>

2’ <o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

1.99<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

61.59<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

121.21<o></o>​

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 21.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-left-alt: solid windowtext .5pt" width=29 noWrap>

3’ <o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

2.98<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

62.58<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

122.20<o></o>​

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 21.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm; mso-border-left-alt: solid windowtext .5pt" width=29 noWrap>

4'<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

3.97<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

63.58<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=62 noWrap>

123.20<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>
Example: on DATA above there are 6 rows and 4 columns, if I enter value on D8 say 122.70 then on D10 it will appear 2° 3.5’ so this also involves interpolation, is this possible in excel? I tried HLOOKUP but it only look from outside then give the inside value of the DATA...<o></o>
can anyone please help...

 

[stanleydgromjr]

mond,

I hope this helps.

Try:
Determining Degrees, Minutes, And Seconds
http://www.cpearson.com/excel/LatLong.aspx

http://www.mrexcel.com/archive/Formulas/21098.html

http://www.ozgrid.com/forum/showthread.php?t=25460


Have a great day,
Stan

 

[mond]

thank you "stan" for a very quick response, but it's not what I need. all that i want is the formula that match down and give the upper value for degrees and left for minutes, anyone please help

 

[c_m]

Try:
<title>Excel Jeanie HTML</title><table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 57px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td> </td> <td> </td> <td style="text-align: center;" colspan="3">Degrees</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td> </td> <td style="font-family: Century Gothic;"> </td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">0</td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">1</td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">2</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: center;" rowspan="5">Minutes</td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">0</td> <td style="text-align: center; font-family: Century Gothic;">0</td> <td style="text-align: center; font-family: Century Gothic;">59.6</td> <td style="text-align: center; font-family: Century Gothic;">119.22</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">1</td> <td style="text-align: center; font-family: Century Gothic;">0.99</td> <td style="text-align: center; font-family: Century Gothic;">60.59</td> <td style="text-align: center; font-family: Century Gothic;">120.22</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">2</td> <td style="text-align: center; font-family: Century Gothic;">1.99</td> <td style="text-align: center; font-family: Century Gothic;">61.59</td> <td style="text-align: center; font-family: Century Gothic;">121.21</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">3</td> <td style="text-align: center; font-family: Century Gothic;">2.98</td> <td style="text-align: center; font-family: Century Gothic;">62.58</td> <td style="text-align: center; font-family: Century Gothic;">122.2</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-family: Century Gothic; font-weight: bold;">4</td> <td style="text-align: center; font-family: Century Gothic;">3.97</td> <td style="text-align: center; font-family: Century Gothic;">63.58</td> <td style="text-align: center; font-family: Century Gothic;">123.2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td> </td> <td> </td> <td> </td> <td style="text-align: right;">122.7</td> <td>2° 3.5'</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>E10</td> <td>=LOOKUP(D10,C3:E3,C2:E2)&CHAR(176)&" "&ROUND(TREND(B3:B7,OFFSET(B2,1,LOOKUP(D10,C3:E3,C2:E2)+1,5,1),D10),1)&CHAR(39)</td></tr></tbody></table></td></tr></tbody></table>

 

[mond]

hello "cm" it works thank you very much.. LOL

 

[mond]

Hello “CM” thanks for helping me with the formula,
but I have another problem how to remove the decimal on minutes I wish to have it as a whole minutes not like say 4º 56.1 must be 4º 56, and is it possible if ever the minutes will become 60, it will automatically convert it to zero then added 1 degree to the degrees value, and another one if is ok for you, cause I wish to expand my datas from 0 to 90 on rows, then 0 to 60 minutes on columns, then what would be the new formula then please help.
Im using Excel 2007