Look up closest match

**tenn0228** · 10-28-2008, 09:55 PM

I have 2 arrays with 2 columns each. In the 1st array I have text field in the 1st column and random #'s in the 2nd column. In the 2nd array I have similar text fields in column 1 and random set of #'s in column 2. In array 2, column 3 I want to find the closest match from array 1 to the # in array 2, column 2 for every text field in column 1 .. again I don't need the larger or smaller #, but the closest match. Please help

Array 1
Column1 Column2
Chris 3
King 16
Twin 5
Chris 70
King 33
Twin 24

Array 2
Column1 Column2 need Result in column 3 (from Array1)
Chris 65 70
King 30 33
Twin 22 24

**sglife** · 10-28-2008, 10:10 PM

I think this is what you need

**tenn0228** · 10-28-2008, 10:52 PM

Can you please help me briefly with what it is doing so I can edit it per my needs. Thank you so much as it is exactly what I was looking for ....

**tenn0228** · 10-29-2008, 07:41 AM

In array 1 if you change 2nd value of King to 17 then it does not work. I guess the spreadsheet you provided does not work if both values are below the value in column 2 of array 2 .. makes sense ?
Please help if I come across such a situation.

**NBVC** · 10-29-2008, 08:26 AM

Try this formula in F2, confirmed with CTRL+SHIFT+ENTER and copied down:

Please Login or Register  to view this content.

**tenn0228** · 10-29-2008, 08:39 AM

AWESOME !!!! It works ... can you please tell me what it is doing briefly ? Thanks a bunch, it sure does work !!

**NBVC** · 10-29-2008, 08:50 AM

The best way to understand what is going on is to use the Formula Auditing tool.

Go to one of the cells containing the formula and then go to Tools|Formula Auditing|Evaluate Formula

Now click Evaluate.... Each time you hit Evaluate, the auditor shows what is getting evaluate and the intermediate results...until you get to the final result.

Basically, the formula is using this: MIN(ABS(IF($A$2:$A$7=D2,$B$2:$B$7,999999)-E2)) to find the minimum difference between your Table 2 and Table 1 numbers... so if A2:A7 matches the name in column D, then it will extract the corresponding number and subtract the value in E2 to get the difference... If the name doesn't match, it uses the large number 999999 (actually, it is probably better to use 9.999999999e+307 instead, as this is the largest number Excel recognizes and is more likely not to interfere with the formula results) to ensure that the difference is not small. The ABS() function turns all the values to their absolute values.

Then the Match() function takes this array and finds the position of that minimum number... and indexes against your table A numbers via the Index() function to get the corresponding number from there.