【Excel】VLOOKUPでデータを横に抽出する方法

Word・Excel

「VLOOKUPみたいな抽出法を、横に使いたい」
「でもVLOOKUPじゃ出来ない」

この記事では、上記の解決方法をご紹介します。

 

Microsoft 365、Excel 2021(※1)では、縦でも横でも反映できる「XLOOKUP」が使用できますが、まだ古いバージョンを使っている方もいらっしゃると思います。

(大きな声じゃ言えませんが、会社でもまだ古いパソコン使ってる部署があったりしますよね)

 

FURIO
FURIO

でも大丈夫!

今回は、古いバージョンでも出来る方法をお伝えするよ!

 

※1:Excel2019でもアップデート等によっては使えるようです
スポンサーリンク
スポンサーリンク

データを横に抽出とは?(HLOOKUP)

縦方向にデータ抽出する「VLOOKUP」に対して、横方向にデータ抽出するのが「HLOOKUP」です。

 

言葉だけではピンとこないと思いますので、分かりやすくご説明します。

下記の図をご覧ください。

1行目に、「商品名・価格・購入希望者」と書かれていますね。

これらのタイトル名に一致した内容を、下記の表から枠内(A2:C8)に反映させたいと思っています。

たとえば、商品名の1行目には「えんぴつ」が自動反映してほしいわけです。

 

▲ この表から該当データを抽出したい。

 

ここでVLOOKUPを使うとエラーになり、うまく機能しません。

 

そこで、HLOOKUPの登場です。

この関数を使うと「商品名・価格・購入希望者」…のように、該当タイトルに一致する内容を、右方向にむかって抽出することができます

 

HLOOKUPを設定する方法

横方向への「抽出」なら、HLOOKUPを覚えておくと便利です。

使い方は基本的にVLOOKUPと同じですが、まったく同じではありません。

 

「HLOOKUPの使い方が分からない」という方は、以下順番に操作してみて下さい。

※後述しますが、HLOOKUPには欠点があります!だったらこの方法じゃなくていい、という方は「VLOOKUPで横検索を行う方法」まで進んでください。

 

HLOOKUPの使い方手順

 データを反映させたいセルを選択します。

範囲の一番左上を選択すると楽なので、今回は「B2」です。

 

HLOOKUPの仕組みは、下記画像をご覧ください。

=HLOOKUP(検索,範囲,行番号,0)

 

「B2」セルを選択し、式のB1をクリック→F4キーを2回押して行を固定。

表示が「B$1」となればOKです。

 

 続けて「Sheet2!A1:G8」をすべて選択し、F4キーを1回押します。

表示が「Sheet2!$A$1:$G$8」となればOKです。

 

 

行番号「2」を消して、「ROW(B2)」を入力します。

※B2は、いま選択しているセルです。

 

 完成したら、 B2セルを「B2:D8」にコピペしましょう。

 

FURIO
FURIO

これで完成!
すべてのセルに反映されたよ!

 



HLOOKUPの欠点

このように、VLOOKUPで反映できなかったことが反映され、HLOOKUPすげー!

……で、終わればいいのですが、このHLOOKUPには、とんでもない欠点があります!

 

それは、並び替えができないこと!

 

ためしにオートフィルタをかけて、A列を降順にすると…なんと、A列しか並び変わりません!

これは致命的…!
本気で頭を抱えるレベル。

 

HLOOKUPは抽出する"だけ"なら都合のいい関数ですが、そのデータを色々使いたいとなると、ちょっと融通がききません。

 

では、横方向に抽出できて、かつ問題なく並び替えを行うためにはどうすればいいでしょうか……実は、その方法があるんです!!

 

FURIO
FURIO

オススメだよ!
抽出法に迷ったら次のやり方を試してみてね。

 

VLOOKUPで横検索を行う方法

 

「クロス抽出」とは?

「クロス抽出」という言葉をご存じでしょうか。

縦・横両方から検索をかけ、クロスした位置にあるセルを対象とする抽出法です。

 

 

上図ですと「定規」の「価格」は「300」(返す値をB5と判断)するわけです。

 

この方法は、VLOOKUPで行います。

手順は以下の通りです。



「クロス抽出」の使い方手順

この方法は、一番左側にナンバーが必要になります。

ない場合はA列を追加し、番号を入力して下さい。

 

同様に抽出元となる表にも番号を入力します。

 

今回のポイントは「MATCH関数」です。

コピペして使っていただく式は、以下の通りです。

=VLOOKUP(列タイトル,抽出元範囲,MATCH(行タイトル,抽出元の行タイトル, 0),0)

 

上記の式だけでは分かりづらいと思うので、画像で確認してみましょう。

 

上記の数式をコピーして、抽出先となる表の「一番左上のセル」に貼り付けます。

 

「列タイトル・抽出元範囲・行タイトル・抽出元の行タイトル」をそれぞれ当てはめてみて下さい。

 

ちなみに見本のエクセルでは、

・行タイトル=A2(1)

・抽出元範囲=Sheet2!A1:G8
・行タイトル=B1(商品名)
・抽出元の行タイトル=Sheet2!A1:G1

を当てはめます。

 

式に間違いがなければ、正しく表示されます。

 

数式の必要個所をF4キーで固定します。

 

B2セルをコピーし、すべてのセルに貼り付けます。

 

ためしに並び替えしてみましょう!
今度は問題なく反映されると思います。

 

FURIO
FURIO

これで作業はすべて終わりだよ!

お疲れさまでした!

 



コメント