=VLOOKUP(F2,A2:D6,3,FALSE)は、A2:D6の最初の列でF2の値を探し、同じ行の3列目の値を返します。最後のFALSEは「完全一致のみ」という意味です。F2で別の商品を選ぶと、価格が変わります。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
G2をクリックすると、表A2:D6が枠で示されます。数式の3を2に変えると、G2は価格の代わりにカテゴリーを返します。Categoryが表の2列目だからです。一致は大文字と小文字を区別しないので、pearでもPearが見つかります。
VLOOKUP関数の構文
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| 引数 | 内容 | この例では |
|---|---|---|
lookup_value(検索値) | 探す値。 | F2(Pear) |
table_array(範囲) | 探す表。VLOOKUPはその最初の列だけを探します。 | A2:D6 |
col_index_num(列番号) | 表の何列目を返すか。表の最初の列を1として数えます。 | 3(Price) |
range_lookup(検索方法) | 完全一致ならFALSEか0。近似一致ならTRUE、1、または省略。 | FALSE |
列番号は、シートのA列からではなく表の始まりから数えます。C列から始まる表では、col_index_numの2はD列を意味します。表の幅より大きい数は#REF!を、0は#VALUE!を返します。
小数点にカンマを使う言語に設定されたExcelでは、引数をセミコロンで区切ります：=VLOOKUP(F2;A2:D6;3;FALSE)。
MATCHで返す列を選ぶ
3を直接書いておくと、誰かが表の中に列を挿入したときに気づかないまま壊れます。数式は3列目を返し続けますが、そこには別のものが入っているからです。代わりにMATCHで見出しから列番号を探させます。ここではG1がドロップダウンリスト(プルダウン)になっていて、StockかCategoryを選ぶとG2もそれに合わせて変わります。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
MATCH(G1,A1:D1,0)は見出し行での「Price」の位置3を返し、VLOOKUPはそれを列番号として使います。Carrotなら0.8です。これは行を商品で、列を見出しで選ぶ2方向の検索です。VLOOKUPの代わりにINDEXで同じ考え方を書く方法は、INDEXとMATCHのページにあります。
近似一致: TRUEを指定したVLOOKUP
最後の引数をTRUEにすると、VLOOKUPは等しい値を探しません。検索値以下の最大の値を探します。税率の区分、成績、送料、手数料の段階のような区分にはこれが必要です。最初の列は小さい順に並べておく必要があります。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
Benの4,200はA列にありません。それを超えない最大の値は1,000なので、Benは3%です。Caraの5,000は5,000の行と完全に一致するので5%です。Devの12,500は最後の区分を超えているので、最後の率の8%になります。最初の区分より小さい値(ここではマイナスの売上)は#N/Aを返すので、表は0から始めています。
$A$2:$B$5の$記号は、F2をF5まで下にコピーしても表を固定します。$がないと、F3はA3:B6を探して最初の区分を飛ばしてしまいます。
4つ目の引数を省略するとTRUEと同じになります。並べ替えていない商品リストでは、これが気づかないバグになります。Excelはリストが並んでいるものとして探すので、違う行の価格を返したり、ある値に#N/Aを返したりします。名前、コード、IDを検索するときは、必ず最後にFALSEを付けてください。
VLOOKUPが#N/Aを返す理由
#N/Aは「見つからない」という意味です。下のシートはよくある原因を3つ示していて、G列ではそれぞれの検索をIFNAとTRIMで囲んでいます。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A 探している値が検索範囲にありません。- 値が表にない。 KiwiはA2:A6にありません。これは本当の「見つからない」で、
IFNA(...,"Not found")で読める文字列に変えられます。E2をAppleに変えると、両方の列に価格が表示されます。 - 余分なスペース。 E3には末尾にスペースの付いた
"Milk "が入っているので、Milkと等しくなりません。TRIM(E3)でスペースを取り除けば、G3で価格が見つかります。スペースが表のほうにあるなら、検索ごとにTRIMを使うのではなく、A列をTRIMで一度きれいにしてください。 - 値が別の列にある。 Fruitはありますが、B列にあります。VLOOKUPは表の最初の列しか探さないので、E4は両方の列で失敗します。表を探す列から始めるか、探す列と返す列を別々に指定するXLOOKUPを使ってください。
検索を囲むならIFERRORではなくIFNAを使います。IFNAは#N/Aだけを受け止めるので、列番号の間違いによる#REF!は「Not found」として隠されず、そのまま表示されます。
ほかにも原因が2つあります：
- 文字列として保存された数値。 A列の商品コードが文字列として入力されていて(取り込んだ後によくあり、隅に小さな緑色の三角形が付きます)、F2に数値の101が入っていると、101がリストにあっても
=VLOOKUP(F2,A2:B6,2,FALSE)は#N/Aを返します。どちらかを変換してください。=VLOOKUP(F2&"",A2:B6,2,FALSE)は文字列の「101」を探し、F2が文字列なら=VLOOKUP(VALUE(F2),A2:B6,2,FALSE)で数値を探せます。 - 並べ替えていないデータでの近似一致。 上の節で説明したとおりです。
VLOOKUPが空白ではなく0を返す
VLOOKUPが行き着いたセルが空だと、Excelは空のセルではなく0を表示します。すると在庫が入力されていないだけなのに、Stock列の0が「在庫切れ」と読まれてしまいます。数式に&""を付けるか、結果の長さを判定します：
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
1つ目は短いですが、返す数値をすべて文字列に変えてしまうので、後のSUMで飛ばされます。2つ目は数値を数値のまま保ちます。
別シートからVLOOKUPする
表の前にシート名と!を書きます。Excelで数式を作るときは、別のシートのタブをクリックして範囲を選択すれば、ExcelがPrices!A2:B6と書いてくれます。ここではOrdersタブがPricesタブの価格を検索しています。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
Pricesタブを開いてAppleの価格を変えると、注文の合計が更新されます。注意点が2つあります：
- スペースを含むシート名は単一引用符で囲みます：
=VLOOKUP(B2,'Price list'!$A$2:$B$6,2,FALSE)。 - 別のブックの表には、角かっこでファイル名を付けます：
[Prices.xlsx]Prices!$A$2:$B$6。そのファイルを閉じると、Excelは数式にフルパスを表示し、保存されたファイルから検索を続けます。
ワイルドカードを使ったVLOOKUP(部分一致)
FALSEを指定しているとき、検索値にワイルドカードを含められます。*は任意の数の文字を、?はちょうど1文字を表します。"*"&E2&"*"は、名前にE2の文字列を含む最初の商品を見つけます。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
「coffee」はIced coffeeとCoffee beansの両方に一致し、VLOOKUPは上から最初のもの$2.90を返します。E2をbeanに変えると$8.50に、juiceにも変えてみてください。本物のアスタリスクや疑問符を探すには、前にチルダを付けます："~*"。
左側の列をVLOOKUPで返す
VLOOKUPは、探す列より左にある列を返せません。col_index_numは右方向にしか数えられず、マイナスの数はエラーになります。価格から商品を探すには、XLOOKUPかINDEXとMATCHを使ってC列を探し、A列を返します：
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
どちらも最初のシートのデータでBreadを返します。詳しくはXLOOKUPで説明しています。
練習: 重さに応じた送料
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
やってみよう: 各送料は、その重さからリストの次の重さまでに適用されます。E2で、VLOOKUPを使ってD2の荷物の重さに対する送料を返してください。
2つの条件でVLOOKUPする
VLOOKUPが受け取る検索値は1つです。2つの列で一致させるには、2つをつなげた作業列を作って表の最初に置き、同じようにつなげた文字列を検索します。下のA列は=B2&"-"&C2を下にコピーしたもので、Coffee-Small、Coffee-Largeなどが入っています。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
やってみよう: A列は商品とサイズをハイフンでつないでいます。G2で、E2の商品とF2のサイズに対応する価格を返してください。
区切り文字が大切です。"Tea"&"Large"はTeaLargeになり、A列のどれにも一致しません。Excel 2021とMicrosoft 365では、=XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6)で作業列なしに検索できます。その方法とINDEX/MATCH版は複数条件での検索で紹介しています。
よくある質問
ExcelでVLOOKUPはどう使いますか？
=VLOOKUP(と入力し、4つの引数を指定します。探す値、表(最初の列にその値が入っている必要があります)、返す列の番号、完全一致を表すFALSEです。=VLOOKUP("Pear",A2:D6,3,FALSE)はA列でPearを探し、その行のC列の値を返します。
VLOOKUPの最後のTRUEとFALSEはどういう意味ですか？
FALSE(または0)は完全一致を求め、値がなければ#N/Aを返します。TRUE(または1、引数の省略)は近似一致を求め、検索値以下の最大の値を返します。これは最初の列が昇順に並んでいるときだけ正しく動きます。
VLOOKUPが#N/Aを返すのはなぜですか？
表の最初の列で値が見つからなかったからです。よくある原因は、打ち間違い、余分なスペース("Milk "は"Milk"と違う)、片方だけ文字列として保存された数値、別の列にある値です。数式をIFNAで囲めば、自分で決めた文字列を表示できます：=IFNA(VLOOKUP(F2,A2:D6,3,FALSE),"Not found")。
VLOOKUPで左側の列を返せますか？
できません。VLOOKUPが返せるのは、表の最初の列より右にある列だけです。Excel 2021かMicrosoft 365なら=XLOOKUP(F2,C2:C6,A2:A6)を、どのバージョンでも使えるのは=INDEX(A2:A6,MATCH(F2,C2:C6,0))です。
別シートからVLOOKUPするには？
範囲の前にシート名と感嘆符を付けます：=VLOOKUP(B2,Prices!$A$2:$B$6,2,FALSE)。シート名にスペースがあるときは単一引用符で囲みます：'Price list'!$A$2:$B$6。
