Finding the nearest match without reusing results
Stealing a great idea from Bernd to shrink the formula:
=IFERROR( INDEX( $A$9:$A$12, MATCH(
MIN(
IF(($B2=$B$9:$B$12)*(0=MMULT(--($A$9:$A$12=TRANSPOSE($F$1:$F1)), ROW($F
$1:$F1)^0)), ABS($C2-$C$9:$C$12))
),
IF(($B2=$B$9:$B$12)*(0=MMULT(--($A$9:$A$12=TRANSPOSE($F$1:$F1)), ROW($F
$1:$F1)^0)), ABS($C2-$C$9:$C$12)),
0)),
"no match")
The above array-entered in F2. (Tested with table2 in A9:C12 as
before).
Bernd's great idea is using IF to generate a mixed array of Diffs and
FALSEs. This is better than Diffs and 0's. Since MIN ignores Boolean
values that are enclosed in an array (this was new to me), FALSE is
not automatically converted to zero. No huge-number substitution is
then required.
- David
|