LOOKUP関数で完全一致の値のみを検索し、値を返すには、他の関数と組み合わせて使う必要があります。ここでは、IF関数とCOUNTIF関数をLOOKUP関数と組み合わせて、完全一致の値を返す方法を説明していきます。
目次
LOOKUP関数でエラーになる原因
LOOKUP関数は検査値が見つからない場合は、ベクトル形式では、検査範囲の中で検査値以下の最大値が返され、検査範囲の最小値よりも検査値 が小さい場合は、エラー値#N/A が返されます。
配列形式では、配列の先頭行または先頭列に含まれている検査値以下の最大値が返されます。検査値 が配列の先頭行または先頭列に含まれている最小値よりも小さい場合は、エラー値#N/A が返されます。
このように、LOOKUP関数では完全一致の値を返すことができません。検査する値が一致しない場合でも、検査値以下の最大値を返してしまうからです
LOOKUP関数ベクトル形式で完全一致の値を返す
LOOKUP関数ベクトル形式で、IF関数、COUNTIF関数と組み合わせて、完全一致の値を返します。
LOOKUP関数ベクトル形式で完全一致の値がある場合
F2に、契約№「20005」の金額を返す式を入力します。返す金額は、契約№「20005」と、一致する値の金額です。
契約№「20005」と一致する値がなかった場合、「エラー」を表示させるようにします。
F2 =IF(COUNTIF(A2:A5,20005),LOOKUP(20005,A2:A5,C2:C5),”エラー”)
「5,000」を返します。
セルを参照する式は次のようになります。
F2 =IF(COUNTIF(A2:A5,E2),LOOKUP(E2,A2:A5,C2:C5),”エラー”)
LOOKUP関数ベクトル形式で完全一致の値がない場合
F3に、契約№「50000」の金額を返す式を入力します。契約№「50000」と一致する値がなかった場合、「エラー」を表示させるようにします。
F3 =IF(COUNTIF(A2:A5,50000),LOOKUP(A2:A5,50000,C2:C5),”エラー”)
「50000」と一致する値がないので、エラーを返します。
LOOKUP関数配列形式で完全一致の値を返す
LOOKUP関数配列形式で、IF関数、COUNTIF関数と組み合わせて、完全一致の値を返します。
LOOKUP関数配列形式で完全一致の値がある場合
F2に、契約№「30125」に対応する金額を返す式を入力します。契約№「30125」と一致する値がなかった場合、「エラー」を表示させるようにします。
F2 =IF(COUNTIF(A2:A5,30125),LOOKUP(30125,A2:C5),”エラー”)
「6,000」を返します。
LOOKUP関数配列形式で完全一致の値がない場合
F3に、契約№「30000」の金額を返す式を入力します。契約№「30000」と一致する値がなかった場合、「エラー」を表示させるようにします。
F3 =IF(COUNTIF(A2:A5,30000),LOOKUP(30000,A2:C5),”エラー”)
「30000」と一致する値がないので、エラーを返します。
LOOKUP関数
エクセルLOOKUP関数は、1行または1列からなるセルの範囲、または配列からある値を見つけるときに使う、検索/行列の関数です。LOOKUP関数の使い方にはベクトル形式と配列からの2種類あります。
LOOKUP関数 ベクトル形式
1行または1列からなるセルの範囲で値を検索します。検査値が見つかると同じ位置にある値を返します。正確な値を得るためには検査範囲を昇順で並び替える必要があります。
ベクトル形式の書式と引数
LOOKUP (検査値，検査範囲, 対応範囲)
| 検査値 | 検査する値を指定します。 |
| 検査範囲 | 検査する値を検索する範囲を、1行または1列で指定します。検査範囲は昇順で並び替えておきます。 |
| 対応範囲 | 検索対象となる範囲を指定します。対応範囲は検索範囲と同じサイズにします。 |
ベクトル形式の使用例
E2に、契約№「20005」に対応する住所を返す式を入力します。
E2 =LOOKUP(D2,A2:A5,B2:B5)
「福岡」を返します。
LOOKUP関数 配列形式
配列の先頭行または先頭列で検索値を検索し、配列の最終行または最終列の同じ位置にある値を返します。配列形式の場合、先頭行または先頭列で昇順で並び替えておきます。
配列形式の書式と引数
LOOKUP (検査値，配列)
| 検査値 | 検査する値を指定します。 |
| 配列 | 検査値と照合するセル範囲を指定します。 |
配列形式の使用例
E2に、契約№「20025」に対応する住所を返す式を入力します。
E2 =LOOKUP(D2,A2:B5)
「大阪」を返します。
IF関数
エクセルIF関数は、指定された条件を評価した結果がTRUE の場合は指定した値を返し、評価した結果が FALSE の場合は別の値を返します。IF関数は、エクセル関数の中で使用頻度が高い関数です。他の関数と組み合わせて使用されることも多いです。
IF関数の書式と引数
IF(論理式,真の場合,偽の場合)
| 論理式 | 条件式を指定します。 |
| 真の場合 | 論理式がTRUEの場合に返す値を指定します。 |
| 偽の場合 | 論理式がFALSEの場合に返す値を指定します。省略するとFALSEを返します。 |
IF関数の使用例
C2に、「国語」の科目が80点以上だったら「合格」、80点未満だったら「不合格」を表示させる式を入力します。
C2 =IF(B2>=80,”合格”,”不合格”)
C2は80点以上なので、「合格」を返します。
COUNTIF関数
COUNTIF関数は範囲から検索条件に一致する値を探しだし、一致するセルの個数を返します。検索条件ではワイルドカードも使用できます。
COUNTIF関数の書式と引数
COUNTIF(範囲,検索条件)
| 範囲 | 検索する範囲を指定します。 |
| 検索条件 | 検索する条件を指定します。 |
COUNTIF関数の使用例
C2に、「A2:B5」の範囲から「地理歴史」の科目を探し、「地理歴史」と一致するのセルの個数を返す式を入力します。
C2 =COUNTIF(A2:B5,”地理歴史”)
「地理歴史」と一致するのセルは２つあるので、２を返します。
LOOKUP関数以外の関数で完全一致の値を返す方法
VLOOKUP関数を使用すると、VLOOKUP関数のみで完全一致の値を返すことができます。VLOOKUP関数の検索方法をFALSEまたは０に指定するだけです。完全に一致する値がない場合はエラー値「＃N/A」が返されます。LOOKUP関数のように検査範囲を昇順で並び替えする必要もありません。完全一致の値を返すことができる関数としてVLOOKUP関数を使用できます。
こちらの記事もご覧ください。⇒エクセルLOOKUP関数がエラーになる原因と対処法！エラー回避
