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

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

HLOOKUP？

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

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

フォーマットをいじっても問題なければ2行目の後ろに1行挿入すると簡単に出来ます。

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

INDEXとMATCHの組み合わせ

INDEXとMATCHを使えば行挿入しなくてもセル結合されたままでも検索可能です。
先に式を示唆しますとこのようになります。

「=INDEX( 3:3 , MATCH( “B” , 1:1 , 0 )+2 )」
解説していきますとINDEX関数は指定された行と列が交差する位置にある値を返します。
INDEX関数について
ここでは行指定は3行目を指定していますので「材料a」行です。

では列指定はどうかというと、MATCH関数を使っています。MATCH関数は指定された列または行から検索した値が何列目、何行目なのかを返します。
ここでは”B”は何列目にあるのか？を検索しており、返される値はE列なので５（列目）です。

それに＋２を足すとG列になりますので、先程のINDEXで３行目のG列を引っ張ってくるようになります。

よって値はG3の「6,000」となるわけです。

パターンAとBではAの方が一般的に解りやすい式になります。関数も１つですし。ただ色んな条件を加味しBを使うこともしばしばあるかと思います。あまり馴染みがない関数ですが覚えると幅は広がりますね。