「○以上△未満」の条件で表引きする（近似検索）…VLOOKUP関数

［Excel 2016/2013/2010/2007］

VLOOKUP関数では、「○以上△未満」の条件で表から検索を行えます。ここでは、「○点以上△点未満の評価は◇」というような得点による評価付けを例に、VLOOKUP関数の使い方を紹介します。

作例…得点に応じて「A、B、C、D」と評価を付ける

作例

図1の得点評価対応表から「70」点に対応する評価を調べます。

VLOOKUP関数の引数［検索値］に検索条件となる「70」を入力したセル「B2」、［範囲］に得点評価対応表のデータが入力されているセル「D3:F6」（「D3:E6」と指定してもOK）、［列番号］に「評価」の列「2」を指定すると、70点に対応する評価「B」が求められます。［範囲］の1列目のデータを小さい順に入力しておき、［検索の型］に「TRUE」を指定すると、［検索値］が見つからない場合に［検索値］未満の最大値が検索されます。

［範囲］から［検索値］を探し、見つかったセルから数えて［列番号］目のデータを求める
=VLOOKUP(検索値, 範囲, 列番号[, 検索の型])

セルB3 | =VLOOKUP(B2,D3:F6,2,TRUE)

図1　VLOOKUP関数を使用して、「70」点に対応する評価を求める。

作例詳解

図1の作例では、次の流れで得点から評価が求められます。

図2　
　(1) 範囲の1列目から検索値を探す。
　(2) 検索値「70」が1列目に存在しないので、「70」未満の最大値である「50」が検索される。

図3
　(3) 検索結果のセル（セルD5)から数えて2列目のデータ「B」を探す。
　(4) VLOOKUP関数を入れたセルに「B」と表示される。

関数解説…VLOOKUP関数

書式

=VLOOKUP(検索値, 範囲, 列番号[, 検索の型])

［範囲］の1列目から［検索値］を探し、見つかったセルから数えて［列番号］目のデータを求めます。［検索の型］では、完全一致検索（FALSE）を行うのか、近似検索（TRUE）を行うのかを指定します。近似検索を行う場合は、［範囲］の先頭列を基準に昇順に並べ替えておく必要があります。

■引数

引数［検索の型］、「FALSE」と「TRUE」の違い

［範囲］の1列目に［検索値］が存在する場合、引数［検索の型］に「FALSE」と「TRUE」のどちらを指定しても、［検索値］に対応するデータが返されます。存在しない場合、以下のような違いが出ます。

■「FALSE」の場合
・［範囲］の左端列は、昇順でなくてもOK。
・［検索値］が見つからない場合、戻り値は「#N/A」（値が未定であることを示すエラー値）になる。
・［検索値］と全く同じデータを探す場合は必ず「FALSE」を指定する。具体例は、「コード番号を検索して表引きする（完全一致検索）…VLOOKUP関数」を参照。

■「TRUE」の場合
・［範囲］の左端列は、昇順で並べ替える必要がある。
・［検索値］が見つからない場合、［検索値］未満の最大のデータが検索される。
・「○以上△未満」の条件で検索する場合に「TRUE」を指定する。

STEPUP…複数の受験者の評価一覧表を作る

図4の成績表のように、VLOOKUP関数を入力したセルをコピーして使う場合は、引数［検索値］を相対参照（「A1」形式）、［範囲］を絶対参照（「$A$1」形式）で指定します。相対参照で指定したセルB3は、コピー先でB4、B5…とずれていきます。一方、絶対参照で指定したセルは、常に「$E$3:$G$6」に固定されます。

先頭のセルに入力する数式
セルC3 | =VLOOKUP(B3,$E$3:$G$6,2,TRUE)

コピー先の数式
セルC4 | =VLOOKUP(B4,$E$3:$G$6,2,TRUE)
セルC5 | =VLOOKUP(B5,$E$3:$G$6,2,TRUE)

図4　VLOOKUP関数の数式をコピーして使い回す場合は、［範囲］を絶対参照で指定して固定する。

関連記事

• コード番号を検索して表引きする（完全一致検索）…VLOOKUP関数