探したいセルの値が何行目にあるか調べる関数
VLOOKUP関数でうまくいかなかった?
だからサイトを探していたらここにたどり着きませんでしたか?
表の中から探したい値を検索する関数といえばVLOOKUP関数ですが、すでに出来上がった表で右に何列目って検索だから、マイナス方向(左へ何列目)って無理ですよね。
だから何か方法はないかと探していたのですよね。
検索型MATCH関数とVLOOKUP関数を使う
MATCH関数は、指定したセルの値を検査範囲内から探して、一致する値を見つけたら何行目にあるかを答えてくれる関数です。
この何行目?って事で通し番号からデータ検索・取得してみよう
エクセル住所録や商品管理などで、一番最初の列って通し番号を入れている場合が多いですよね。
A列に通し番号、B列に名前、C列に商品コード(レンタル)とします。
この表を商品コードA-0001から順に誰が借りているかをリスト化したいのですが、データベースになっているこの表自体を並び替えるわけにはいかない事ありませんか?
そうすると、右にもう1つ表を作ることになり、G列に商品コードA-0001から順に並べてあります。
この表で、C列にレンタル商品のコードが入力されると、F列にMATCH関数により列番号が取得されて、VLOOKUP関数によりH列に商品コードを借りている顧客番号とI列に名前が表示されるようにします。
MATCH関数で何列目かを取得
セルF2にMATCH関数を入れてみると、
- 検査値がセルG2の「A-0001」
- 検索範囲がセルC2:C21のレンタル列各セル
- 照合の種類は完全一致の「0」
となりますので、数式にすると下記のようになります。
1行目が項目名で2列目に通し番号「1」から登録を始めるので、検索範囲をこの2行目からスタートすると、2行目の通し番号「1」が1列目となります。
そうすることにより、一番最初の列の番号が取得できるのでVLOOKUP関数が使えるようになりますよ!
VLOOKUP関数で列番号から名前を取得
検査値の行番号が取得できれば、登録番号から右に何列目の値を取得することが可能になりますのでVLOOKUP関数が使えるようになります。
- 検査値がMATCH関数で取得した列番号セルF2の値
- 検査範囲がデータベースのセルA2:B21
- 顧客番号を取得するので1列目のセルA2
- 完全一致の「0」
名前を取得するのは2列目にするだけですね。
全てのセルにMATCH関数とVLOOKUP関数を入れると、商品番号からレンタルしている人の名前を取得できるようになりました。
住所録に後から追加したらVLOOKUP関数で左に検索できなかったので、この方法でできるようになりました。
とは言え、改良したエクセル住所録では最初から想定して作っている途中なんですけどね。