LOOKUP - with a difference!

[VoG]

Hello. I have a problem that I believe is soluble using VBA but I would really prefer a formula approach. This is an example of my sheet with several columns removed for clarity.

<b>Z207</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:59px;" /><col style="width:64px;" /><col style="width:78px;" /><col style="width:64px;" /><col style="width:42px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </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></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Result</td><td >Easting</td><td >Northing</td><td >SampleDate</td><td >Dist (m)</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">49.63</td><td style="text-align:right; ">359265</td><td style="text-align:right; ">181460</td><td style="text-align:right; ">10/05/2004</td><td style="text-align:right; ">694</td><td > </td><td style="text-align:right; ">2004</td><td style="text-align:right; ">123</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">94.68</td><td style="text-align:right; ">361223</td><td style="text-align:right; ">182613</td><td style="text-align:right; ">24/06/2004</td><td style="text-align:right; ">1579</td><td > </td><td style="text-align:right; ">2005</td><td style="text-align:right; ">33</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ff0000; text-align:right; ">123</td><td style="text-align:right; ">359850</td><td style="text-align:right; ">181834</td><td style="text-align:right; ">02/08/2004</td><td style="text-align:right; ">0</td><td > </td><td style="text-align:right; ">2006</td><td style="text-align:right; ">80.15</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">66.1</td><td style="text-align:right; ">360362</td><td style="text-align:right; ">181769</td><td style="text-align:right; ">26/10/2004</td><td style="text-align:right; ">516</td><td > </td><td style="text-align:right; ">2007</td><td style="text-align:right; ">67.3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">35.98</td><td style="text-align:right; ">363199</td><td style="text-align:right; ">180609</td><td style="text-align:right; ">29/11/2004</td><td style="text-align:right; ">3566</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">27.31</td><td style="text-align:right; ">362832</td><td style="text-align:right; ">180672</td><td style="text-align:right; ">23/02/2005</td><td style="text-align:right; ">3200</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">33</td><td style="text-align:right; ">359355</td><td style="text-align:right; ">181969</td><td style="text-align:right; ">11/04/2005</td><td style="text-align:right; ">513</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">31.11</td><td style="text-align:right; ">362593</td><td style="text-align:right; ">180297</td><td style="text-align:right; ">25/05/2005</td><td style="text-align:right; ">3144</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">81.2</td><td style="text-align:right; ">361525</td><td style="text-align:right; ">183027</td><td style="text-align:right; ">06/10/2005</td><td style="text-align:right; ">2056</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">42.48</td><td style="text-align:right; ">361588</td><td style="text-align:right; ">181688</td><td style="text-align:right; ">15/11/2005</td><td style="text-align:right; ">1744</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">20.92</td><td style="text-align:right; ">362252</td><td style="text-align:right; ">180143</td><td style="text-align:right; ">31/01/2006</td><td style="text-align:right; ">2938</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">48.55</td><td style="text-align:right; ">361353</td><td style="text-align:right; ">181673</td><td style="text-align:right; ">27/07/2006</td><td style="text-align:right; ">1512</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">139.6</td><td style="text-align:right; ">363038</td><td style="text-align:right; ">180926</td><td style="text-align:right; ">13/09/2006</td><td style="text-align:right; ">3315</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">80.15</td><td style="text-align:right; ">360326</td><td style="text-align:right; ">181898</td><td style="text-align:right; ">03/11/2006</td><td style="text-align:right; ">480</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">32.93</td><td style="text-align:right; ">360802</td><td style="text-align:right; ">181710</td><td style="text-align:right; ">11/12/2006</td><td style="text-align:right; ">960</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">23</td><td style="text-align:right; ">362232</td><td style="text-align:right; ">181339</td><td style="text-align:right; ">25/01/2007</td><td style="text-align:right; ">2433</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">22.6</td><td style="text-align:right; ">365264</td><td style="text-align:right; ">181066</td><td style="text-align:right; ">06/03/2007</td><td style="text-align:right; ">5468</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">39.7</td><td style="text-align:right; ">362376</td><td style="text-align:right; ">180837</td><td style="text-align:right; ">25/04/2007</td><td style="text-align:right; ">2716</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">67.3</td><td style="text-align:right; ">359849</td><td style="text-align:right; ">181418</td><td style="text-align:right; ">07/06/2007</td><td style="text-align:right; ">416</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">22.6</td><td style="text-align:right; ">361283</td><td style="text-align:right; ">181706</td><td style="text-align:right; ">14/12/2007</td><td style="text-align:right; ">1439</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=SQRT(<span style=' color:008000; '>(B2-B$4)</span>^2+<span style=' color:008000; '>(C2-C$4)</span>^2)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


What I have done here manually is as follows.

1) Identified the highest result (column A) occurring in 2004.

2) Calculated the distance between each sample point (formulas in column E) and the point identified in 1)

3) For each year 2005, 2006, 2007 found the result in column A corresponding to the closest point (i.e. smallest distance) compared to the sample identified in 1)

4) Filled in the matching values in columns G and H.

Is this even possible using a formula

I've done half a dozen of these by hand but I think that my client may want many (hundreds ) more.

Thanks in advance.

 

[RoryA]

Is it OK to add a new column to extract the year part of the sample date? If so, assuming the new year column is in D and the others are shifted one across accordingly, then I would use:
in I1, array-entered: =MATCH(MAX(IF($D$1:$D$21=2004,$A$1:$A$21,"")),IF($D$1:$D$21=2004,$A$1:$A$21,""),0)
In F2 (Dist column): =SQRT((B2-INDEX(B:B,$I$1))^2+(C2-INDEX(C:C,$I$1))^2)
and copy down
In I4 (the 2004 match) array-entered: =INDEX(A:A,MATCH(MIN(IF($D$1:$D$21=H2,$F$1:$F$21,"")),IF($D$1:$D$21=H2,$F$1:$F$21,""),0))
and copy down.
Does that help at all?

 

[VoG]

Fantastic!

Thanks very much Rory

 

[EugeneCarter]

Just as a simple (if not eligant) solution... I'd insert a date column (A:A) =year(sampledate)
Then I'd sort the table by year/distance
Then for G:H (which'd become H:I because of the insert of column A:A, i would put I2 =VLOOKUP(H2,$A$2:$F$21,2,FALSE) .. it'll find the first (i.e. largest) by year.

Alternatively, you can put a pivot table in, and select MIN(dist(m)) as your data points, but you'd still need that 'year' column.

Sorry can't think of anything more eligant right now.

 

[Yogi Anand]

Hi Vog II:

following is one way using one-varible DataTable ...

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Result	Easting	Northing	SampleDate	Dist (m)			123
2	49.63	359265	181460	10/5/2004	694		2004	123		FALSE
3	94.68	361223	182613	24/06/2004	1579		2005	33
4	123	359850	181834	2/8/2004	0		2006	80.15
5	66.1	360362	181769	26/10/2004	516		2007	67.3
6	35.98	363199	180609	29/11/2004	3566						database
7	27.31	362832	180672	23/02/2005	3200						criteriaRange
8	33	359355	181969	11/4/2005	513						dataTable_columNinputCell
9	31.11	362593	180297	25/05/2005	3144						dataTable_formulaCell
10	81.2	361525	183027	6/10/2005	2056						dataTable: G1:H5
11	42.48	361588	181688	15/11/2005	1744
12	20.92	362252	180143	31/01/2006	2938
13	48.55	361353	181673	27/07/2006	1512
14	139.6	363038	180926	13/09/2006	3315
15	80.15	360326	181898	3/11/2006	480
16	32.93	360802	181710	11/12/2006	960
17	23	362232	181339	25/01/2007	2433
18	22.6	365264	181066	6/3/2007	5468
19	39.7	362376	180837	25/04/2007	2716
20	67.3	359849	181418	7/6/2007	416
21	22.6	361283	181706	14/12/2007	1439
Sheet3

</body></html>
Would this do?

 

[VoG]

Thanks Eugene and Yogi for those alternatives.

I should have explained that I only posted part of the data for 1 'zone'. I actually have up to about 200 zones per sheet, and 26 sheets. With that layout it is much easier to use Rory's formulas to the right of the existing columns.

Thanks again