最後に一致する値を検索する

ほとんどの検索では、見つかった最初の一致が返されます。しかし、商品の最新価格、最新のステータス更新、顧客の最後の登録内容など、最後の一致が必要になることもあります。

リストが時間とともに増え、同じキーが何度も登場する場合、通常は一番下の行が最新の情報です。標準のVLOOKUPや完全一致のMATCHでは、代わりに先頭の行が頑固に取得されます。

このレッスンでは、最後に一致する値を取得する、信頼性の高いいくつかの方法を紹介します。

MATCH(value, range, 0)は上から下へ検索し、最初に見つかった完全一致で停止します。「Apple」が2行目、5行目、9行目にある場合、MATCHは2を返します。

キーが一意であればこれで問題ありませんが、新しい行は無視されます。最後の出現箇所に到達するには、下から検索するか、最後に一致した位置を返す方法が必要です。

最新バージョンのExcelまたはGoogle Sheetsを使用している場合は、XLOOKUPを使うと簡単です。5番目と6番目の引数で、一致モードと検索方向を指定します。

検索モードの引数に-1を渡すと、最後から最初へ検索します。するとXLOOKUPは、一番下にある一致キーに対応する値を返します。

ここでは、G1の商品をA2:A10から検索し、B2:B10から対応する価格を返します。検索は一番下から開始されます。

古いスプレッドシートでは、LOOKUPに数値2と、条件を使った割り算を組み合わせる、よく知られたテクニックが使えます。

1/(A2:A10=G1)という式は、一致する行では1を生成し、一致しない行では割り算エラーを生成します。LOOKUPは、存在するどの値よりも大きい値である2を検索することでエラーを通り過ぎ、最後に有効な1に到達し、B2:B10から対応する値を返します。

1/(A2:A10=G1)の動きを順に確認しましょう。

• キーが一致する行では、1/TRUE = 1になります。
• 一致しない行では、1/FALSE = #DIV/0!エラーになります。

LOOKUPはエラーを無視し、検索対象の2が見つからない場合は、最後のエラーではないエントリに対応する結果を返します。一致する値はすべて1なので、最後の1が選ばれ、最後に一致した行の値が得られます。

INDEX-MATCHの組み合わせを使うこともできます。考え方は、最後に一致した位置を見つけ、その位置をINDEXに渡すことです。

MATCHの中で同じ割り算のテクニックを使い、1/(A2:A10=G1)に対して2を検索すると、最後に一致した行の位置を取得できます。その位置を、値を返す列のINDEXに渡します。

MATCHの3番目の引数を省略すると、既定値は1になります。これは、昇順データに対する近似一致を意味します。MATCHは、2以下で最大の値を検索します。

1/(A2:A10=G1)の配列には、1とエラーしか含まれません。2以下で最大の値は1なので、MATCHはそのような1の最後の位置を返します。その位置が、最後に一致した行そのものです。

A2:A10に時間の経過とともに記録された「Order-7」の注文ステータスが並び、B2:B10にステータスのテキストが入っているとします。「Order-7」は3行目、6行目、9行目にあります。

• 一致配列では、3行目、6行目、9行目が1になり、それ以外はエラーになります。
• MATCH(2, ...)は、範囲の先頭から数えた位置として、最後の一致である9を返します。
• INDEXはその最後の行、つまり最新のステータスを返します。

どの方法を使うべきでしょうか。

• XLOOKUP with -1：使用できる場合に最もすっきりして読みやすい方法です。
• LOOKUP(2, 1/...)：特別なバージョンを必要とせず、ほぼどこでも使えます。
• INDEX-MATCH(2, 1/...)：位置も必要な場合や、別の列から値を返したい場合に便利です。

3つの方法はすべて同じ結果を返します。使用できるツールと、数式に求める読みやすさに応じて選んでください。

次の点に注意してください。

• 範囲のサイズが不一致：条件範囲と返却範囲の高さが同じでないと、行の対応がずれます。
• 隠れた重複：末尾のスペースがあると、「Apple 」と「Apple」は異なる値になります。まずTRIMでテキストを整えてください。
• 一致する値がない：一致するものがない場合、このテクニックはエラーを返します。IFERRORで囲むと、わかりやすい代替結果を表示できます。

最後の一致を取得するテクニックは、2つの条件と組み合わせることもできます。割り算の中で条件テストを掛け合わせると、両方のキーを満たす行だけが1になります。

たとえば、商品がG1と一致し、かつ地域がG2と一致する、最も新しい価格を見つける場合です。LOOKUP(2, ...)のテクニックで、条件を満たす最後の行を取得できます。

同じ商品が複数の地域に登場する、タイムスタンプ付きのログで便利です。

最後の一致を取得する検索について理解できているか確認しましょう。

最初の一致ではなく最後に一致する値を返すには、次の方法を使います。

• 使用できる場合はXLOOKUP(..., -1)を使い、下から上へ検索します。
• どのバージョンでも使える従来のLOOKUP(2, 1/(range=key), result)テクニックを使います。
• 位置も必要な場合は、INDEX(result, MATCH(2, 1/(range=key)))を使います。

範囲のサイズをそろえ、余分なスペースを取り除き、安全のため数式をIFERRORで囲むことを忘れないでください。