VLOOKUP関数で２番目以降の値を抽出する２－数式の解説
このページは「VLOOKUP関数で２番目、３番目以降の値を抽出する数式」を解説する2ページ目です。
ここでは、あえてVLOOKUP関数で2番目以降のデータを抽出する方法を説明します。
１．VLOOKUP関数で２番目の値を抽出した例
まずは、VLOOKUP関数を使って「該当する２番目の値を抽出」した例を見てみましょう。
上の表から「正社員の2番目の人」、つまり「A列の〇のついている2番目の人」という条件で「№3の元木さん」を抽出してみましす。
図がちょっと小さくて見づらいですが、VLOOKUP関数で確かに「〇が2番目に付いた元木さん」が抜き出せています。
２．２番目の値を抽出する数式の作り方
次に、2番目の値を抽出する数式の作り方です。
２－１．数式の形は
まず、2番目以降の値を抽出できる数式の形は、次の通りです。
かなり長い数式で、分かりづらいと思います。
そこで、この数式をコピー・貼り付けしてから、下の例を見てそれぞれ引数を指定してください。
２－２．引数ごとの指定内容
次に、例を見ながらそれぞれの引数の指定している内容を確認してみましょう。
① 検索値
検索値は、VLOOKUP関数での普段の指定の仕方と同じです。
例では、「A列が〇の人」なので「"〇"」となっています。
② 範囲内左上の列番号
範囲内左上の列番号は、「範囲内の一番左上のセルの列番号」を指定します。
例では、表の範囲が「A5:E14」なので、「A」となります。
なお、これを「"（ダブルクォーテーション）」で囲ってください。
③ 検索範囲
検索範囲は、そのままですが「検索する範囲」を指定します。
例では、「検索値（〇）が入っている範囲」なので「A5:A14」となります。
④ 抽出する順位
抽出する順位は、その名の通り「何個目の値を抽出したいか？」を入れます。
例では、「〇が付いた２番目の人」を抽出したいので「2」になります。
⑤ ②の行番号-1の値
ここには「②の行番号値-1の値」が入ります。
例では②の行番号は「5」だったので、「5-1＝4」となります。
⑥ 範囲内右下のセル番号
ここには「範囲における一番右下のセル番号」を指定します。
例では表の範囲が「A5:E14」なので「E14」となります。
なお、前に「:（コロン）」を付け、全体を「"（ダブルクォーテーション）」で囲んでください。
※ 例）「:"E14"」
⑦ 列番号
列番号は、VLOOKUP関数での普段の指定の仕方と同じで、「範囲のうち左から何番目の列の値を表示させるか？」を入れます。
例では「２番目の『№』」を抽出したいので「2」になっています。
⑧ 配列数式にする
最後に大事なのは「配列数式にする」ことです。
配列数式にするには「数式を入力し終わった後、ShiftキーとCtrlキーを押しながらEnterキーを押す」操作が必要です。
そして、数式の前後に「{}」が付けば成功です。
結果、例だと数式は、
{=VLOOKUP("〇",INDIRECT("A"&MATCH(LARGE((A5:A14="〇")*1/ROW(A5:A14),2),1/ROW(A5:A14),0)+4&":E14"),2,0)}
となります（見やすいよう「$」は除いています）。
これで、「２番目の値」がVLOOKUP関数で抽出できるようになります。
３．３つ目以降を抽出できる数式は
次に、３番目以降の値を抽出する方法です。
先ほど紹介した数式が作れたのであれば、後は簡単です。
「④抽出する順位」の値を変えればいいのです。
例えば、先ほどの数式の「④抽出する順位」を「3」にしたのが上です。
結果、「正社員3番目の人」である「№4の向井さん」が抽出できました。
そして、「④抽出する順位」をセル参照にすれば、そのセルの値を変えるだけで好きな順番の値を抽出することができるようになります。
上の例では、セルG8の値で「④抽出する順位」を変えれるようにしています。
これで、より使いやすくなるでしょう。
次のページでは、ここで紹介した数式でなぜ2番目以降を抽出できるか、数式の説明をします。
【口コミ・評判のいいExcel本の紹介】
VLOOKUP関数のその他の記事はこちらです。
- VLOOKUP関数その他の記事はこちら
- ◎基本的な使い方
- ◎「以上/未満」の条件指定の仕方
- ◎〇番台の条件にする方法
- ◎部分一致の条件指定の仕方
- ◎別シートから値を抜き出す方法
- ◎別ブックから値を抜き出す方法
- ◎数式コピーで横方向の値を簡単に取得する方法
- ◎２番目、３番目以降の値を抽出する方法
- ◎＃N/Aエラーになる３つの原因と対処方法
- ◎＃REF!エラーになる原因と対処方法
- ◎＃VALUE!エラーになる原因と対処方法
- ◎空白を０ではなく空白で表示する２つの方法
- ◎「IF(ISERROR(VLOOKUP」の意味と使い方
- ◎IFERROR関数とVLOOKUPの組み合わせ方
- ◎空白でもエラーでも空白で表示する方法
- ◎複数条件にする方法
- ◎セルや範囲を固定しコピーのズレを防ぐ方法
- ◎文字列を条件にする方法
- ◎列番号の意味と指定の仕方