今回のまとめ
・VLOOKUP関数は、検索して跳ね返す値のある列が必ず右側になければいけない、という制約がある。・レガシーエクセルでVLOOKUP関数の制約を克服する定番の方法は二つある。
①作業列を作る方法
②INDEX+MATCHの組み合わせを使う方法
①作業列を作る方法
②INDEX+MATCHの組み合わせを使う方法
前回(第2回)、「VLOOKUP関数」をご紹介しました。ただし、「左側の列を検索できない」という最大の弱点があることも述べました。
今回、VLOOKUP関数の弱点を「レガシーエクセル」で克服する2つの方法をご紹介します。
前回(第2回)解説のとおり、VLOOKUP関数で縦・垂直(Vertical)方向に検索することができますが、VLOOKUP関数は検索して跳ね返す値のある列が必ず右側になければいけない、言い換えれば「左側の列を検索できない」という制約があります。図1をご覧ください。
図1 A列~C列が「販売データ」。G列~H列が「得意先マスタ」。
「販売データ」には「得意先コード」(B列)はあるが、「得意先名」列がないので表示したい。
だが、「得意先マスタ」は「得意先コード」(H列)の左側に「得意先名」(G列)がある。これではVLOOKUP関数で対処できない。
事例の場合、「販売データ」(A列~C列)の「得意先コード」(B列)と同じ項目列を、「得意先マスタ」(G列、H列)の「得意先コード」(H列)で検索し、「得意先マスタ」の「得意先名」(G列)を跳ね返したいわけですが、「得意先マスタ」の列の並びを見ると検索される「得意先コード」(H列)の左側に跳ね返したい「得意先名」(G列)があるため、このままではVLOOKUP関数では対処できないことになります。
最も初歩的な対処法として、この「得意先コード」の右隣り(I列)にVLOOKUP関数を使うためだけの「作業列」を作成するという方法があります。
図2をご覧ください。「得意先コード」(H列)の右隣りに「得意先名」(I列)をコピーしたり、セル参照したりすれば、検索される列(H列の「得意先コード」)の右側に跳ね返したい列(I列の「得意先名」を示すためだけの「作業列」)があるので、VLOOKUP関数が使えるようになります。セルI4を見ると分かるように、「=G4」としてここではセル参照をしています。
ただ、この方法には一つ大きな問題があります。「作業列」が一列追加されるため、データ量が多い場合、動作が遅くなるなどの問題が生じてしまいます。
図2 「作業列」(I列)を作りVLOOKUP関数が動作するようにした
「作業列」を作ってVLOOKUP関数を動作させる方法は、非常に簡単で便利です。ただし、VLOOKUP関数を動作させるため「だけ」に「作業列」をわざわざ一列作るというのもいかがなものでしょうか。
そこで、エクセルに詳しい人は「INDEX関数+MATCH関数」の合わせ技でVLOOKUP関数のような効果を得ようとします。この方法であれば、検索される値の列(事例では「得意先マスタ」の「得意先コード」(H列))と跳ね返したい値の列(同「得意先名」(G列))がどんな位置関係にあっても検索できるようになります。
ここで、それぞれの関数を説明します。
INDEX関数は、テーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。
つまり、INDEX関数を使えば、指定した行と列が交差する位置にあるデータを抽出できるわけです。その書式は、INDEX(配列,行番号,列番号)です。詳しくは、Microsoft公式一次情報も参考にしてください。
INDEX 関数(microsoft.com)
MATCH 関数は、範囲のセルの範囲で指定した項目を検索し、その範囲内の項目の相対的な位置を返します。
つまり、指定した検査値が検査範囲の中で何番目のセルにあるかを求める、これがMATCH関数です。詳しくは、Microsoft公式一次情報も参考にしてください。
MATCH 関数(microsoft.com)
これらINDEX関数とMATCH関数を組み合わせて使うことで、VLOOKUP関数と同様の効果を得られます。
事例の場合、VLOOKUP関数では不可能な検索される左側の値を跳ね返す、つまり「得意先マスタ」の検索される「得意先コード」(H列)の左側に存在する「得意先名」(G列)の値を「販売データ」(D列)に表示することが「INDEX+MATCH」で可能となります。
図3 INDEX+MATCHによる検索の結果(D列)。
INDEX+MATCHの使い方については、Microsoft公式一次情報も参考にしてください。
VLOOKUP、INDEX、または MATCH を使って値を検索する(microsoft.com)
確かに、INDEX関数とMATCH関数を組み合わせれば、「作業列」を作らずに、VLOOKUP関数と同じような効果を得ることができます。これが「レガシーエクセル」による「値検索」の最適解の一つなのですが、数式が複雑で、一見して理解が及ばず、エクセル初学者にはハードルが高いようにも思います。
次回(第4回)は、前回(第2回)と今回(第3回)でご紹介した「レガシーエクセル」による値検索の方法よりもスマートな「モダンExcel」による対処法をご紹介します。
*「モダンExcel」の詳細は、拙著『モダンExcel入門』(日経BP)などに委ねます。
モダンExcel入門(日経BP)
https://amzn.to/3wnlzBD
『モダンExcel研究所』
https://excel.value.or.jp/
村井 直志(むらい ただし)
公認会計士 / データアナリスト
日本公認会計士協会東京会コンピュータ委員会委員長、経営・税務・業務各委員会委員、第三者委員会委員などを歴任。研究テーマ『CAAT(コンピュータ利用監査技法)で不正会計に対処する、エクセルを用いた異常点監査技法』で日本公認会計士協会研究大会に選抜。
『3000社の決算を分析してきた会計士が教える 経営を強くする 会計7つのルール』(ダイヤモンド社)
『Excelによる不正発見法 CAATで粉飾・横領はこう見抜く』(中央経済社)
『会計ドレッシング10episodes』(東洋経済新報社)
『モダンExcel入門 データ分析&可視化の新しい教科書』(日経BP)など
公認会計士とは?経済社会を支えるプロフェッショナルの仕事観とキャリアに迫る
ジャスネットキャリア編集部
【2025年最新】公認会計士に合格しても就職できない?監査法人に落ちたその後の現実と対処法
公認会計士 齊藤 健太郎
【最新版】公認会計士 合格後にやるべきこと完全ガイド|試験終了からキャリア設計まで
公認会計士 齊藤 健太郎
CFOになるには? 必要なスキル・資格・キャリアパスを徹底解説!
公認会計士 齊藤 健太郎
監査法人とは何かを徹底解説!仕事内容・年収・転職・求人情報まで
公認会計士 齊藤 健太郎
監査トレーニーとは?仕事内容・年収・キャリアパスを徹底解説!
公認会計士 齊藤 健太郎
【会計士が一番最初に読む IPO入門講座】第3回 IPO準備の基本(3):IPO準備において求められる体制
公認会計士 齊藤 健太郎
公認会計士が非常勤の仕事をずっと続けるためのコツキャリアナビ閉鎖後の対策とは?
公認会計士 齊藤 健太郎
公認会計士の就職・転職|17種類のキャリアパスを徹底解説!
公認会計士 齊藤 健太郎
「公認会計士はやめとけ」は本当か?後悔しないために知るべき現実と成功の道
公認会計士 齊藤 健太郎
公認会計士のワークライフバランスが一番とれる働き方は?~職場環境ごとの「3つの必要条件」を徹底比較~
公認会計士 齊藤 健太郎
修了考査とは?受からない人の5つ特徴と、その対策3選
公認会計士 齊藤 健太郎
合格できないのには理由がある?!公認会計士試験に受からない人の特徴。その傾向と対策8選
公認会計士 齊藤 健太郎
【コーポレート・ガバナンス 頻出用語解説 ⑧】関連当事者取引とは?その範囲から把握する方法まで
公認会計士 齊藤 健太郎
公認会計士試験浪人の末路は?挫折パターンと3つの対応策
公認会計士 齊藤 健太郎
【コーポレート・ガバナンス 頻出用語解説 ⑦】内部監査とは?その導入と運用、手順まで
公認会計士 齊藤 健太郎
【コーポレート・ガバナンス 頻出用語解説 ⑥】内部統制とは?企業が内部統制を機能させ、仕組み上の不備、業務上の不正をみつける方法は
公認会計士 齊藤 健太郎
転職に規模は関係ない?!あなたに合う会計事務所の選び方【決定版】
ジャスネットキャリア編集部
【コーポレート・ガバナンス 頻出用語解説 ⑤】内部通報制度、内部告発の違いとは?
公認会計士 齊藤 健太郎
東京証券取引所の市場再編後、上場を目指す企業の選択肢は?
公認会計士 齊藤 健太郎
公認会計士による上場企業CFO&CSOの業務内容、仕事の魅力は?
常川 陽介
【企業が知っておかなければならない】反社会的勢力の調べ方と関係遮断の方法
公認会計士 齊藤 健太郎
【年間200冊読む会計士が選ぶ!】公認会計士の新たなキャリア戦略に役立つオススメ本5選
公認会計士 江黒 崇史
公認会計士によるCAOの業務内容、仕事の魅力は?
公認会計士 山本 隆史
公認会計士の目 ~会計ニュース分析「金融庁、銀行融資の緩み点検へ コンプラ違反倒産急増で」を読んで
公認会計士 齊藤 健太郎
【コーポレート・ガバナンス 頻出用語解説 ④】社内規程の作り方とは? ~コーポレートガバナンス体制の維持・強化のために
公認会計士 齊藤 健太郎
東京都内、近郊で開催されている士業交流会、その目的と選び方とは?オススメの交流会の紹介
公認会計士 ジャスネットキャリア編集部
公認会計士による「証券会社における引受審査業務」という仕事の魅力、その業務内容とは?
公認会計士 齊藤 健太郎
【コーポレート・ガバナンス 頻出用語解説 ③】これでわかる!予算編成の流れとは?
公認会計士 齊藤 健太郎
【コーポレート・ガバナンス 頻出用語解説 ②】取締役会の運営:月次決算の早期化について
公認会計士 齊藤 健太郎
公認会計士が税理士法人、税理士事務所に転職する際に気をつけるべきこと
公認会計士 ジャスネットキャリア編集部
【コーポレート・ガバナンス 頻出用語解説 ①】取締役会の構成員とその資質
公認会計士 齊藤 健太郎
コーポレートガバナンスとは?3つの目的と効果、コーポレートガバナンス・コードが何のかをわかりやすく解説
公認会計士 齊藤 健太郎
離職率の高い会計事務所、定着率の高い会計事務所はどんなところ?
ジャスネットキャリア編集部
【最新】IPOにおける監査法人変遷 2023年の振り返り
公認会計士 江黒 崇史
未経験でも可能? 社会人になってから監査法人に転職する方法とは
ジャスネットキャリア編集部
USCPA(米国公認会計士)の資格取得、2024年度からの新制度について
ジャスネットキャリア編集部
USCPA(米国公認会計士)の年収は?監査法人、税理士法人から事業会社まで
公認会計士 福留 聡
USCPA(米国公認会計士)が活躍できる転職先と、そのメリット、デメリットは?
公認会計士 福留 聡
公認会計士のための「国会議員政策担当秘書」仕事の魅力、業務内容は?
公認会計士 三宅 博人
公認会計士のための英語勉強法
公認会計士 野村 宜弘
公認会計士が監査法人で『非常勤』として働く!そのメリットは?
公認会計士 中島 英明
【令和7年最新】公認会計士試験事情
ジャスネットキャリア編集部
公認会計士の業務分野~その魅力とやりがいとは?
公認会計士 ジャスネットキャリア編集部
PAIB(組織内会計士)とは?~各界にはばたく公認会計士たち~
公認会計士 ジャスネットキャリア編集部
公認会計士に向く人、向かない人とは?
公認会計士 齊藤 健太郎
タイプ別:公認会計士の仕事に求められる適性
公認会計士 ジャスネットキャリア編集部
公認会計士のための「実務家教員」の業務内容、仕事の魅力は?
公認会計士 三宅 博人
監査法人の業務についていけない…。その3つのパターンと3つの対処法は?
公認会計士 荻野 光
「大手企業での内部監査部門」の業務内容、仕事の魅力は?
ソフトバンク ロボティクス グループ株式会社 監査室 監査室長 生川 治
「社外CFO」の業務内容、仕事の魅力は?
公認会計士 村瀬 功
公認会計士と税理士の違いとは? ~3つの観点(業務内容、クライアント、キャリア)からの比較
ジャスネットキャリア編集部
独立系財務・会計コンサルティングファームの業務内容~事業再生業務編~
公認会計士 山田 勝也
「ベンチャー企業CFO」の業務内容と仕事のやりがいは?
公認会計士 岡 義人
「資産税コンサル」の業務内容、仕事の魅力は?
公認会計士 金井 義家
「上場企業(大手企業)経営企画部門」の業務内容、仕事の魅力は?
公認会計士 岡 義人
常勤監査役の業務内容、仕事の魅力とは?~公認会計士の職域は会計監査だけではない
公認会計士 石田 正
公認会計士が語る TOKYO PRO Market(東京プロマーケット)とは?
公認会計士 新開 智之
中堅・中小監査法人 マネージャーの業務内容、仕事の魅力とは?
公認会計士 江黒 崇史
独立系財務・会計コンサルティングファームの業務内容~IPOアドバイザリー業務編~
公認会計士 山田 勝也
BIG4から中小監査法人へ⁈ 近年のIPOにおける監査法人の変遷
公認会計士 江黒 崇史
「企業の海外進出支援」の仕事の魅力、業務内容は?
公認会計士 萱場 玄
「初めて相続税を担う税理士(公認会計士)」のための最低限の不動産に関わる知識
不動産鑑定士 冨田 建
新規公開4割減 株価低迷響く、東証再編効果 道半ば
公認会計士 新開 智之
「スタートアップ・ベンチャー企業支援」の業務内容、仕事の魅力は?
公認会計士 伊藤 英佑
新規公開時にプロ評価 東証が改革案 先端技術、審査円滑に
公認会計士 新開 智之
IPOコンサルティングの仕事の魅力、業務内容
公認会計士 重見 亘彦
独立系財務・会計コンサルティングファームの種類と分類(「運営体制」×「業務領域」)
公認会計士 山田 勝也
「BIG4監査法人 アドバイザリー部門」の業務内容、仕事の魅力は?
公認会計士 島尻 将史
福岡のヌーラボ、株価低迷 IPO、成長の加速課題に スタートアップに難関
公認会計士 新開 智之
「大手監査法人の金融部門」での業務内容、仕事の魅力は?
公認会計士 岩波 竜太郎
「上場企業(大手企業)管理会計部門」の業務内容、仕事の魅力は?
公認会計士 梅澤 真由美
「上場企業(大手企業)経理部」での業務内容、仕事の魅力は?
公認会計士 福原 俊
米で過去最大のSPAC、調達資金5400億円返還 市場激変で合併断念
公認会計士 新開 智之
「NPO法人CFO」の業務内容、仕事の魅力は?
認定NPO法人フローレンス ディレクター/CFO 公認会計士 横山 正宏
「大手監査法人のパブリック部門」での業務内容、仕事の魅力とは?
公認会計士 髙梨良紀
「大手監査法人の国際部」での業務内容、仕事の魅力は?
公認会計士 本岡
【第3回】監査や経理の担当者が知っておくべき!『モダンExcel』(全10回)
公認会計士 村井 直志
「経営企画 M&A部門」の業務内容、仕事の魅力とは?
公認会計士・税理士 小栗 弘義
【第2回】監査や経理担当者が知っておくべき!『モダンExcel』データ分析超入門(全10回)
公認会計士 村井 直志
福証「IPO挑戦隊」に3社入会
公認会計士 新開 智之
「外資系企業ファイナンス&アカウンティング部門」での業務内容、仕事の魅力は?
管理会計ラボ株式会社 梅澤 真由美
自民「未上場株の流通市場を」スタートアップ支援策
公認会計士 新開 智之
「国際税務コンサルティング」の業務内容、仕事の魅力は?
公認会計士・税理士 赤塚 孝江
中堅・中小監査法人 パートナーの業務内容、仕事の魅力とは?
監査法人アヴァンティア 橋本 剛
準大手監査法人 パートナーの業務内容、仕事の魅力とは?(後編)
太陽有限責任監査法人 石原 鉄也
準大手監査法人 パートナーの業務内容、仕事の魅力とは?(前編)
太陽有限責任監査法人 石原 鉄也
「独立系M&Aコンサルティング」の業務内容、仕事の魅力とは?
アヴァンセコンサルティング株式会社 代表取締役 野村 昌弘
大学発の起業 慶応が最多 昨年度、2位は岐阜大 200社新設、累計3,300社に 経産省調査
公認会計士 新開 智之
「国内系大手税理士法人(税理士法人山田&パートナーズ、辻・本郷税理士法人)」での業務内容、仕事の魅力は?
公認会計士 高橋 善也
会計士が活躍できる「フォレンジック」の業務内容、仕事の魅力とは?(Big4系アドバイザリーファーム編)
公認会計士 野村 宜弘
BIG4監査法人での「IPO支援」の業務内容、仕事の魅力とは?
公認会計士 福留 聡
「独立系会計コンサルティング」の業務内容、仕事の魅力とは?
アヴァンセコンサルティング株式会社 代表取締役 野村 昌弘
中堅・中小監査法人 アドバイザリーの業務内容、仕事の魅力とは?
公認会計士 江黒 崇史
2022/3/12 エニーマインド上場延期 ウクライナショックで
公認会計士 新開 智之
新株予約権「信託型」が3倍 昨年のIPO企業、22社が導入
公認会計士 新開 智之
【第1回】監査や経理担当者が知っておくべき!『モダンExcel』データ分析超入門(全10回)
公認会計士 村井 直志
東証プライム、1841社上場 基準厳しく 新陳代謝狙う。1部の8割強が東証プライム移行、改革道半ば
公認会計士 新開 智之
初めての株式鑑定評価~公認会計士として、意識しなければならない点とは?【第2回】
公認会計士 冨田 建
新規株式公開(IPO)の価格を決める実務を見直す議論について
公認会計士 新開 智之
初めての株式鑑定評価~公認会計士として、意識しなければならない点とは?【第1回】
公認会計士 冨田 建
会計士のための非常勤役員、社外役員になる方法
公認会計士 江黒 崇史
コロナ禍における会計士のテレワーク事情
公認会計士 江黒 崇史
会計士のための「WEB会議できちんと見える」コーディネート
公認会計士 Rody
【会計士が一番最初に読む IPO入門講座】第2回 IPO準備の基本(2):IPOできる会社・できない会社の違い
公認会計士 齊藤 健太郎
【会計士が一番最初に読む IPO入門講座】第1回 IPO準備の基本(1):IPOをする目的の整理
公認会計士 齊藤 健太郎
監査法人でパートナーになるには ~会計士が出世するためのポイント3選
公認会計士 江黒 崇史
【連載】独立会計士のための はじめての税務実務 第3回 申告調整
公認会計士 小林 正和
横領や粉飾決算は、なぜ起こる? 会計士が果たす役割は?
公認会計士 福留 聡
【連載】独立会計士のための はじめての税務実務 第2回 役員報酬
公認会計士 小林 正和
上場企業以外でIFRSの適用をしているケースと中小企業版IFRS
公認会計士 福留 聡
女性会計士が産休をとって復帰するまで(税理士事務所・独立編)
公認会計士 西濱 絢
女性会計士が産休をとって復帰するまで(監査法人編)
公認会計士 西濱 絢
IPOの株価決定「入札方式」「ブックビルディング方式」とは?
公認会計士 福留 聡
会計士・税理士が注目する国際資格 公認内部監査人(CIA)とは?
公認会計士 福留 聡
【連載】独立会計士のための はじめての税務実務 第1回 源泉徴収税額(源泉税)
公認会計士 小林 正和
監査法人のリファード業務とは?
公認会計士 福留 聡
【会計業界動向2024】公認会計士の総人数は増えてる?減ってる?
社会人が会計士試験に独学で合格するために知っておくべきこと
公認会計士 石動 龍
監査法人5年目までの公認会計士がぜったい読んでおきたい7冊の本
公認会計士 江黒 崇史
AIが会計士と税理士の仕事を奪う?!~IT先進国エストニアに行ってみて考えた会計業界の未来~
税理士 小島 孝子
会計士のワークライフバランス・年収について知っておくべき人生戦略
公認会計士 松本 佑哉
監査法人を退職する前に知っておきたい「5つ」のこと(後編/これからのキャリアを描く)
公認会計士 江黒 崇史
監査法人を退職する前に知っておきたい「5つ」のこと(前編/キャリアの振り返り)
公認会計士 江黒 崇史
公認会計士が独立して事務所経営に失敗しないための開業準備ノウハウ(仕事の受注方法と報酬単価の決定について)
公認会計士 福留 聡
公認会計士の独立開業って儲かるの?収入源と年収のホントのトコ
公認会計士 伊藤 英佑
会計士業界年収動向 2024【監査法人編】
公認会計士と税理士の違いとは?仕事内容・年収・クライアント・キャリアの違いを徹底比較
公認会計士 福留 聡
公認会計士に中国語は必要?中国語を活かせる仕事は?
公認会計士 山本 真美子
実はグローバル?監査法人での英語の活用法
公認会計士 岩波 竜太郎
女性会計士は、ママになっても働ける?子育てとの両立とキャリアアップについて
公認会計士 M.N
公認会計士が独立・開業前に知っておくべきポイント解説
公認会計士 冨田 建
一般企業で公認会計士に任される仕事って何?
公認会計士 都外川 雅門
組織内会計士の働き方とは?監査法人を選ばない道
公認会計士 横山 敬子
監査法人の離職率は高い?監査法人や辞めたあとに広がる進路・転職先
公認会計士 福留 聡
監査業務は激務?4大監査法人と中小監査法人のワークスタイル分析
公認会計士 安田 憲生
監査法人とは?もう迷わない監査法人の選び方(後編)大手と中小を徹底比較!
公認会計士 江黒 崇史
監査法人とは?もう迷わない監査法人の選び方(前編)大手と中小を徹底比較!
公認会計士 江黒 崇史
監査とは?監査法人・会計士の役割と業務内容について
公認会計士 江黒 崇史
公認会計士試験を諦めた後の具体的な進路について
公認会計士試験に受からない。撤退するタイミングの判断
公認会計士が取るべきダブルライセンスは?弁護士・不動産鑑定士など同時に持つメリット
公認会計士 福留 聡
CPDとは?継続的専門能力開発制度について
公認会計士 中島 英明
公認会計士の修了考査|合格後の会計士登録までに必要なこと3選
公認会計士 R.H
遅すぎる? 社会人で公認会計士を目指しても大丈夫?
公認会計士 高橋 善也
公認会計士試験に受かりやすい大学とは?
公認会計士 白土 英成
公認会計士になるには?資格学校(専門学校)の選び方
ジャスネットキャリア編集部
【令和5年最新】公認会計士試験について知っておくべきこと6選~受験前から合格後の流れ
公認会計士
公認会計士の資格に関するカテゴリーごとにまとめたページです。
カリスマ公認会計士が語る
5人の会計士が伝授!
試験の乗り越え方と合格後について
自ら転職を経験した会計士たちが、
タイプ別に自分を活かす働き方を解説
プラスαで身につけるべきスキルは?
ダブルライセンスを持つ会計士が指南
転職や独立したら年収はどうなる?
開業会計士が教える成功のノウハウ
辞めた後のキャリア、デザインできていますか?
今だから語れる「私が監査法人を辞めた理由」
4,000社を超える取引実績から分析!
公認会計士を募集する求人動向
公認会計士4,000人の相談実績から分析!
あなたの次のステップは?
ここが違う、公認会計士のための
応募書類と面接対策
公認会計士が〇〇してみた。
実体験を交えたトレンドを解説
コーポレートガバナンスとは?
編集部がピックアップ!
「Accountant's Magazine」は、著名な会計プロフェッションにスポットをあて、その人生観・仕事観を紹介。会計・経理分野に従事する人と仕事の将来像を提示する、読者と共に考えるヒューマンドキュメント誌です。今なら新規登録していただくと、「Accountant's Magazine」(WEB版)の全記事を無料で閲覧することができます。