There's probably a more elegant way to do this, but here is at least an inelegant solution. The formula becomes big, so I'll explain it in pieces.
![enter image description here](../../I/static/images/ff5d3642e440bf10d290635c54dffbe13bfc14e5ed51d09bd9be6bb43a1e074f.png)
I wanted to illustrate how this works, so I kept the range manageable. Scale it to your needs.
My data array is D1:F10. I just filled it with consecutive numbers because that was easy, and made illustration clear. The formula doesn't rely on the numbers being integers or in a specific order. I replaced several of the values with duplicates of some of the matching values to verify that the formula works.
In column A are a few target values--one where the closest match rounds down, an exact match, one where the closest match rounds up, and one where the target is equidistant between two closest matches. I picked target values to have a match in each column.
Column B contains the closest match in the array for each target value (the solution).
The formula is built from components, so I'll explain the components, which are shown in this grid:
![enter image description here](../../I/static/images/5aa5a43a39f7d82f433541352def69eb7234b4ca38a0482397e559956c509171.png)
The target values are replicated in column A. Column B finds the minimum difference between the target value and the closest value in the table. Formula in B16:
{=MIN(ABS($D$1:$F$10-A1))}
This is an array formula. It's a component of the final formula, so the final formula needs to be entered with Ctrl Shift Enter.
We don't know which direction the difference is in, whether we need to add or subtract to match the closest value in the table, so we try both. To find the matching value by "rounding down", it uses the formula in column C. To find the matching value by "rounding up", it uses the formula in column D. One subtracts and one adds the difference:
=SUMPRODUCT(($D$1:$F$10=A1-B16)*$D$1:$F$10)
=SUMPRODUCT(($D$1:$F$10=A1+B16)*$D$1:$F$10)
If the first parentheses is true, it will evaluate to 1
, otherwise it will be 0
. SUMPRODUCT multiplies this with the associated cell value and adds up the results.
But notice some odd results.
- Several targets return zero, or no match. Those are cases where the match is found by rounding in the other direction.
- The target that was mid-way between the two closest values returns the lower matching value rounding down and the higher matching value rounding up. If you care which one gets picked, sequence the formula components so rounding in the preferred direction is selected.
- Several values are double what they should be. These are the cases where I duplicated the closest value; the formula adds in every one.
To deal with these cases, we count how many matches are found. This is done in columns E and F:
{=SUM($D$1:$F$10=A1-B16)}
{=SUM($D$1:$F$10=A1+B16)}
These components are also array formulas. The formula in column E evaluates whether each cell value is equal to the target minus the difference, which is either True or False (1
or 0
), and sums those values. The formula in column F is the same except it tests against the target plus the difference.
We can employ these results in two ways: to test for no match (zero result), in order to select which result to use, and as a count of matches to correct the inflated value of multiple matches in the previous step.
Note that since we're finding the closest match, there will always be a "closest", so rounding up or down can't both return zero. We only need to test one of the results. If it is zero, we use the other. If it isn't zero, we use that value since the other value will be either zero, the same, or another valid result that can be selected via the order of the test.
For the selected valid result, the count is used as a divisor. So the formula for the result is:
=IF(E16=0,D16/F16,C16/E16)
So that's how it works. Turning this into a single, consolidated formula is a matter of replacing cell references with the formula in the referenced cell. Here is what the consolidated formula looks like (I'll add spacing and line feeds to make it more readable; you would want to remove those if you want to copy and paste the formula):
{=IF(SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))=0,
SUMPRODUCT(($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1))),
SUMPRODUCT(($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1))))}
By closest, you mean smallest difference in either direction? If so, you can combine the SMALL function with the ABS function applied to the difference between the target and array values. If there is an exact match, the difference of zero is the smallest possible difference. I don't have time to develop a solution, but, for example,
{=SMALL(ABS($A1-$D$1:$Z$25),1)}
would find the minimum difference, so worst case, look for the exact match of A1+diff and A1-diff. – fixer1234 – 2018-07-16T21:51:34.710Could you share some sample data to find EXACT value? – Rajesh S – 2018-07-17T12:24:09.260