- Home »
- エクセル関数の技 »
- VLOOKUP関数で左側の列から値を検索する
- VLOOKUP関数で表示されるエラー値#N/Aを表示しないようにする方法をいくつか説明します。
2022/11/19
- VLOOKUP関数の構文は
=VLOOKUP(検索値,参照値を含む範囲,戻り値を含む範囲内の列番号,近似一致 (TRUE) または完全一致 (FALSE))
となっていて、検索値を含む範囲は左端の列である必要があります。
戻り値は右側にある指定した列から値を返すことができます。
- このページでは、検索する列より左側にある列の値を返す方法を説明します。
一般的な方法は、INDEX関数とMATCH関数を組み合わせる方法です。
Excel2021,Excel for Microsoft365 では、XLOOKUP関数が利用できます。
- 他にもちょっと変わった方法を書いてみますが、一般的ではありませんので、こんな方法もあるのかといった程度で読み飛ばせばよいと思っています。
- このページでは数式が読みやすいようにデータはテーブルにしています。
テーブル名は Tbl_商品表 としています。
|
B |
C |
D |
E |
| 2 |
番号 |
分類 |
商品 |
単価 |
| 3 |
1001 |
果物 |
りんご |
200 |
| 4 |
1002 |
果物 |
みかん |
300 |
| 5 |
1003 |
果物 |
バナナ |
400 |
| 6 |
2001 |
野菜 |
大根 |
500 |
| 7 |
2002 |
野菜 |
白菜 |
600 |
| 8 |
2003 |
野菜 |
キャベツ |
700 |
- D列の商品を検索して、一致する商品のB列の番号(検索する列の左側にあるB列の番号)を返します。
ここは一般的なINDEX関数とMATCH関数の組み合わせです。
H3セルの数式は
=INDEX(Tbl_商品表[番号],MATCH(G3,Tbl_商品表[商品],0))
としました。
セル範囲では =INDEX(B3:B8,MATCH(G3,D3:D8,0)) となります。
- IF({1,0},を検索すると検索結果では中国語のページが目につきます。あちらで流行っているのかもしれません。
列を入れ替えた配列を返するテクニックです。
- H3セルの数式は =VLOOKUP(G3,IF({1,0},Tbl_商品表[商品],Tbl_商品表[番号]),2,0) と入力します。
セル範囲では =VLOOKUP(G3,IF({1,0},D3:D8,B3:B8),2,0) となります。
- ポイントは下図のように、
IF({1,0},Tbl_商品表[商品],Tbl_商品表[番号]) の部分で商品と番号の列を取り出した配列を返しています。
この配列を VLOOKUP関数で検索しています。
- Spillが利用できないバージョンではセル範囲を選択して、数式を[Shift]+[Ctrl]+[Enter]で入力すると、配列が返されているのを確認することができます。
- Excel2021,Excel for Microsoft365 では、XLOOKUP関数が利用できます。
VLOOKUP関数より柔軟な検索が可能になっており、MicrosoftではこちらXLOOKUP関数の使用を勧めています。
- XLOOKUP関数の構文は以下のようになっています。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- H3セルには
=XLOOKUP(G3,Tbl_商品表[商品],Tbl_商品表[番号])
と入力しています。
セル範囲では =XLOOKUP(G3,D3:D8,B3:B8) となります。
- VLOOKUP関数と組み合わせて、Excel for Microsoft365で使用できる CHOOSECOLS関数を利用することもできます。
- 最新で使用できるCHOOSECOLS関数をつかって、配列を入れ替えることが可能になりました。
Choosecols関数の構文は =CHOOSECOLS(array,col_num1,[col_num2],...) です。
H3セルには =VLOOKUP(G3,CHOOSECOLS(Tbl_商品表,3,1),2,0) と入力しています。
セル範囲では =VLOOKUP(G3,CHOOSECOLS(B3:E8,3,1),2,0) となります。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数の技 »
VLOOKUP関数で左側の列から値を検索する
PageViewCounter
Since2006/2/27