エクセルLOOKUP関数が昇順で並び替えていない場合の対処法

エクセルLOOKUP関数は、昇順で並び替えずに検索することはできません。昇順でないと、正しい値を返してくれません。昇順で並び替えずに検索するには、VLOOKUP関数を使います。ここでは、列を検索するVLOOKUP関数と、行を検索するHLOOKUP関数の使い方を紹介します。

 

 

VLOOKUP関数

VLOOKUP関数は、指定した範囲の1列目で値を検索し、指定した列から同じ行にある値を返します。

VLOOKUP関数の書式と引数

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

検索値検査する値を指定します。
範囲検索する範囲を指定します。その場合、検索する列を範囲一番左にします。検索の型をTRUEに指定、または省略した場合は、範囲の一番左の列は昇順で並び替えておく必要があります。昇順で並び替えないと正確な値を見つけることができません。
列番号範囲の一番左の列から数えた、列番号を指定します。
検索の型 FALSEまたは”0”を指定すると、一致する値のみ返されます。一致する値が見つからない場合は、エラー値#N/A が返されます。TLUEまたは”1”を指定すると、完全に一致する値か、近似値が返されます。省略した場合もTRUEが返されます。完全に一致する値が見つからない場合は、検索値未満の最大値が返されます。

 

 

エクセルVLOOKUP関数の使用例

 

F2に、「福岡」支社の「金額」を表示させる式を入力します。

F2 =VLOOKUP(D2,A2:C5,3,FALSE)

F2には「8,000」が返されます。

「F3:F5」の範囲にF2の式をコピーする場合は、「A2:C5」の範囲を絶対値にします。

F2 =VLOOKUP(D2,$A$2:$C$5,3,FALSE)

上の式を「F3:F5」にコピーします。

結果は、表のとおりです。

 

HLOOKUP関数

エクセルHLOOKUP関数は、指定した範囲の一番上の行を検索し、検索値と一致した値と同じ列にある、指定した行から値を返します。

HLOOKUP関数の書式と引数

HLOOKUP(検索値, 範囲, 行番号, [検索の型])

検索値検索する値を指定します。検索値には、値、参照、または文字列が指定できます。
範囲検索する値が含まれる範囲を指定します。検索値は範囲の一番上の行で検索されます。検索の型をTRUEに指定、または省略した場合は、範囲の上の行は昇順で並び替えておく必要があります。並び替えないと正確な値を見つけることができません。
行番号返したい値が含まれる行の番号を指定します。範囲の一番上から数えて何番目かを指定します。
検索の型TRUEまたはFALSEを指定します。TRUEは完全一致の値か近似値を返します。省略した場合もTRUEを返します。完全一致の値が見つからない場合、検索値未満の最大値を返します。FALSEは完全一致の値を返します、完全一致の値が見つからないとエラー値 #N/A が返されます。

 

エクセルHLOOKUP関数の使用例

 

H2に、「福岡」支社の「金額」を表示させる式を入力します。

H2 =HLOOKUP(G1,A1:E3,3,FALSE)

H2には「8,000」が返されます。

「I2:J2」の範囲にH2の式をコピーする場合は、「A1:E3」の範囲を絶対値にします。

H2 =HLOOKUP(G1,$A$1:$E$3,3,FALSE)

上の式を「「I2:J2」にコピーします。

結果は、表のとおりです。

 

 

LOOKUP、HLOOKUP、VLOOKUP

エクセルLOOKUP関数、HLOOKUP関数、VLOOKUP関数とは、どんな関数でしょう。

HLOOKUP:データを横方向に検索します。行番号を指定して、その行から同じ列の値を返します。

VLOOKUP:データを縦方向に検索します。列番号を指定して、その列から同じ行の値を返します。

LOOKUP  :ベクトル形式と配列形式の2種類の使い方があります。

・ベクトル形式は、1行または1列の範囲から値を探しだし、同じ位置にある値を返します。

・配列形式は、先頭行または先頭列で値を検索し、配列の最終行または最終列の同じ位置にある値を返します。

エクセルLOOKUP関数、HLOOKUP関数、VLOOKUP関数の使い分け

エクセルLOOKUP関数は、HLOOKUP関数、VLOOKUP関数のように。完全一致になる値を取り出すことができません。LOOKUP関数の引数には、HLOOKUP関数、VLOOKUP関数にある「検索の型」がないからです。

HLOOKUP関数、VLOOKUP関数は「検索の型」で「TRUE]か「FALSE」を指定できますが、LOOKUP関数は、「検索の型」で「完全一致の値を返す」または「近似値を返す」といった選択がないのです。完全一致の値がないと、無条件に検査範囲内で検査値以下の最大値が、一致する値として返されてしまいます。

検索する範囲を昇順で並び替えたくない場合は、HLOOKUP関数、VLOOKUP関数を、値を検索する行または列が、検索範囲の一番上の行、または一番左の列にない場合は、LOOKUP関数の使用を考えましょう。

こちらの記事もご覧ください。⇒エクセルLOOKUP関数がエラーになる原因と対処法!エラー回避