最も近い数値を探し、同距離の候補も残す

9に最も近い数値を探すと、8と10はどちらも差が1です。小さい側だけを探すと10を見落としてしまいます。JOIN SHEETでは候補との数値差を二乗して最小値を求め、その値に一致する行を結合し直すことで、同じ近さの候補をすべて残せます。

近い値は小さい側とは限らない

架空のTargetsは2行、Referencesは3行です。すべての候補と比較できるよう、読み込む前に両表のMatchAll列へ数値1を手入力します。TargetIDとReferenceIDは各原本で一意にします。この後、全候補の比較表をCandidates、対象ごとの最小値表をMinimumsという名前で保存します。

Targets

TargetID	Target	MatchAll
T1	9	1
T2	15	1

References

ReferenceID	ReferenceValue	MatchAll
R1	8	1
R2	10	1
R3	20	1

候補の距離と最小距離を別々に保存する

TargetsとReferencesを読み込み、MatchAll同士を「一致する行のみ」で結びます。2×3=6候補です。両方のMatchAll列を非表示にし、TargetとReferenceValueが数値であることを確認します。
「結果を整理」→「計算」でDelta = ReferenceValue − Targetを作ります。次に「掛け算 · A × B」でAもBもDeltaにし、名前をSquaredにします。
2つの計算を適用した6行をCandidatesとしてExcelに保存します。列はTargetID・Target・ReferenceID・ReferenceValue・Delta・Squaredです。この全候補のファイルを次の集計結果で上書きしないでください。
同じ作業で「結果を整理」を開き直し、作成済みの計算の下に「集計」を追加します。TargetIDでまとめ、Squaredの「最小値」をMinSquaredとします。適用後、T1=1、T2=25の2行を別のMinimumsファイルに保存します。
新しい作業でCandidatesとMinimumsを読み込みます。TargetID同士をつなぎ、さらにSquaredとMinSquaredも接続して「一致する行のみ」を選びます。同じ対象で、距離の二乗がその対象の最小値と一致する候補だけが残ります。Minimums側のTargetIDだけを非表示にします。
「結果のプレビュー」でT1のR1・R2、T2のR2・R3の計4行を確認して保存します。保存・集計・再読込はそれぞれ手動で行います。

同じ距離なら両方の候補を残す

9には8と10が、15には10と20が同じ最小距離で残ります。Squaredは距離そのものではなく距離の二乗です。正負が逆でも大きさが等しい差は同じ二乗値になり、大きい側の候補を見落としません。

TargetID	Target	ReferenceID	ReferenceValue	Delta	Squared	MinSquared
T1	9	R1	8	-1	1	1
T1	9	R2	10	1	1	1
T2	15	R2	10	-5	25	25
T2	15	R3	20	5	25	25

候補数と数値精度を先に確認する

最初の候補数は、対象の件数×参照する値の件数です。この例は6行ですが、大きな表では急増するため、先に件数を確認してください。空欄や数値でない値の扱いも決めておきます。小数や非常に大きな数を使う場合は、計算精度と保存後の値を照合してください。

この方法では最も近い候補をすべて残します。1件に絞りたい場合は、同じ距離ならどちらを優先するかというルールが別途必要です。

自分のファイルで始めましょう

JOIN SHEETを使ってみる ↗

Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。

サンプルの検証日: 2026.10.07

出典

Find nearest value like LOOKUP? (Reddit r/excel)

原文は入力より小さいか大きいかに関係なく、実際に最も近い数値を返す検索方法を尋ねています。上の表と手順は、この質問を参考に別途作成した架空の例です。質問者の実データや製品の利用体験ではありません。