HLOOKUPで検索結果から横2列ずれた値を返す

数式 / 関数

e16-0.png
2次元テーブルのLOOKUP
表を作っていてこういう表を扱ったことはありませんか?今回はこういった2次元テーブルでBの材料a金額「6,000」を引っ張ってくる方法です。
2パターンあります。
e16-1.png

HLOOKUP?

通常であれば=HLOOKUP(A1:G5 , “B” , 3 , 0) くらいの式でいいんですが、この例ですと1行目のところ(B:DおよびE:G)をセルの結合してしまっています。これだと先程の式の返す値はE列の数量150を返すようになってしまいます。結合のセルの場合、一番頭のセルとして判定されるからです。
e16-2.png

行挿入が出来るならばパターンA

フォーマットをいじっても問題なければ2行目の後ろに1行挿入すると簡単に出来ます。
e16-3.png
挿入し、1行目の種類と2行目の項目をくっつけます。式はB3セルだと「=B1 & B2」になります。
これでHLOOKUPで検索出来るようになります。
「=HLOOKUP(A3:G5 , “B金額” , 2 , 0)」
ただし、行挿入などフォーマットをいじるとマクロが不具合を起こす可能性がありますので、簡単に挿入などが出来ない場合があります。その場合はパターンBで可能です。

INDEXとMATCHの組み合わせ

INDEXとMATCHを使えば行挿入しなくてもセル結合されたままでも検索可能です。
先に式を示唆しますとこのようになります。
e16-4.png
「=INDEX( 3:3 , MATCH( “B” , 1:1 , 0 )+2 )」
解説していきますとINDEX関数は指定された行と列が交差する位置にある値を返します。
INDEX関数について
ここでは行指定は3行目を指定していますので「材料a」行です。
e16-5.png
では列指定はどうかというと、MATCH関数を使っています。MATCH関数は指定された列または行から検索した値が何列目、何行目なのかを返します。
ここでは”B”は何列目にあるのか?を検索しており、返される値はE列なので5(列目)です。
e16-6.png
それに+2を足すとG列になりますので、先程のINDEXで3行目のG列を引っ張ってくるようになります。
e16-7.png
よって値はG3の「6,000」となるわけです。
e16-8.png
パターンAとBではAの方が一般的に解りやすい式になります。関数も1つですし。ただ色んな条件を加味しBを使うこともしばしばあるかと思います。あまり馴染みがない関数ですが覚えると幅は広がりますね。

コメントをどうぞ

メールアドレスが公開されることはありません。

前の記事
「シート名が長すぎます」
次の記事
MATCH
封筒 印刷 履歴書 STDEV 無料 偏差値 ワイルドカード テンプレート ネットプリント 図形 フロー図 COLUMN NA() クイズ SUMIF COUNTIF VLOOKUP SUM コピペ 配列数式 グラフ TODAY 条件付き書式 目標 標準報酬月額 ユーザー定義関数 バーコード 検索 作業効率UP XLSTART
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑