EXCELのHLOOKUP関数の意外な使い道

Excelで表（テーブル）検索といえばVLookUpさんの独擅場？

と思っていました。
HLookUpなんて要らん子だと
ところが、ある条件を満たす場合はVLookUpで検索できないけれどHLookUpでは検索できる場面があります。

VLookUp関数の弱点

そもそもプログラマーだとテストデータを作成するのにExcelをよく利用するかと思います。なんせリレーショナルなデータを作成するのだから参照しまくりで作った方がデータの変更時は楽ですしね。
で、よく使うのがVLookUp関数なのですが、こいつの弱点は検索キーを左端に持ってこないといけないという事。で、そもそもDBの設計で必ずPKが1項目で済むというのは少なく、複数項目でPKとするテーブルが多くなります。なのでVLookUpに固執すると左端にPK列を結合したものを用意してVLookUpするのですが、PK以外で検索する場合もあります。となると左へ追加追加していってくと、正直見づらくなります。特にスクロールの固定をしたい場合はPK列を固定とするのでそれより左の列は全部常に表示される状態になりますので、検索用の見る必要のない列を残す羽目になります。列を非表示にしたり幅を0にしたりして何とかならなくもないのですが、気分的に邪魔なものは邪魔なのです。そこで、条件付きですがHLookUpさんの出番となります。

HLookUp関数って左端の列が見出しで項目は行でレコードが列なんじゃねーの？

通常はそのように使用します。で、キーは本来は項目となる一番上の行を指定するはずなのです。

それはそうと、テーブル好きですか？僕は構造化参照まで含めて大好きです

セル範囲をA1:H36とか書いても一々その範囲を見ないとわからないから嫌いなんですよね。だもんでテーブル名[#データ]とか=[@参照したい列名]とかで指定できる構造化参照を愛用しています。これExcel2007以降で追加された機能でテーブルに対して使用できます。詳細はググってもらうとして大事なのはテーブルにすると一般的なVLookUpで検索しやすい行がレコードの表になります。そして大事なのが列名は一番上の見出し行で重複しません。先の構造化参照で列名を指定に使うので、重複したらどっちやねん！？状態になるため、重複した見出しを入れようとしても後ろに番号が振られて必ずユニークになるのです。・・・おや？HLookUpさんは一番上の行を検索して探すんじゃぁ・・・

テーブルなら実は検索でHLookUpも使えます

発想の転換。VLookUpもHLookUpも関数の解説では検索するキーデータをもとに最初の項目で検索してレコードを特定し、欲しい項目を指定して値を取得とありますが、テーブルを使えば項目名がユニークになるので、欲しい項目名をキーとして検索して項目を特定し、VLookUpでよく使用するMatchを使って列番号を取得するのと同じような感じでレコードを特定することができてしまいます。これのメリットはレコードの特定には端の項目である必要が無いという事です。なので、検索用に結合したデータを置く項目は左端にする必要が無いので右の方に逃がしてしまえるのです。

で、結局どうやればいいの？

以下の通り

=HLOOKUP("内容",t一覧[#すべて],MATCH(F1,t一覧[管理番号],0)+1,FALSE)

「"内容"」は欲しい項目名を文字列で指定
「t一覧」はテーブル名
t一覧[管理番号]の「管理番号」が検索したい項目名
「F1」が検索したいデータの入ってるセル
「Match」の結果に＋１しているのは検索対象が見出しも含めた[#すべて]にしているので、1行下へずらす必要があるためなのでした。

ぶっちゃけ、Index＋Matchの方が良さ気なんだけど、HLookUpだって行レコードの形式でも検索できるよってかVLookUpよりも融通が効くからむしろHLookUpとVLookUp逆の方が便利になりそうじゃね？ってお話