VLOOKUPを使用すると、常に最初の一致が得られます。 INDEXMATCH関数でも同じことが起こります。では、2番目のマッチまたは3番目またはn番目のVLOOKUPをどのように行うのでしょうか。

この記事では、範囲内の値のN番目のオカレンスを取得する方法を学習します。

0055

一般式

{=SMALL(IF(range=value,ROW(range)-ROW(first_cell_in_range)+1),n)}

注:これは link:/ excel-array-formulas-arrays-in-excel-formula [arrayformula] です。 CTRL + SHIFT + ENTERで入力する必要があります。範囲:値のn番目の位置を検索する範囲*。

値:範囲内でn番目の位置を探している値。 First_cell_in_range:範囲内の最初のセル。範囲がA2:A10の場合、範囲の最初のセルはA2です。*

n:値の発生数。

物事を明確にするための例を見てみましょう。

===

例:Excelで2番目の一致を見つける

だからここに私はExcelの範囲A2:A10の名前のこのリストを持っています。この範囲に名前を付けました。ここで、名前の中で2番目に出現する「Rony」の位置を取得したいと思います。

0056

上の画像では、範囲A2:A10(名前)の7番目の位置にあることがわかります。次に、excel式を使用してその位置を取得する必要があります。

上記の一般式をC2に適用して、リスト内で2番目に出現するRonyを検索します。

{=SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2)}

CTRL + SHIFT + ENTERで入力してください。

0057

そして、私たちは答えを持っています。正しい7を示しています。 nの値を3に変更すると、8になります。範囲内の値の出現よりも大きいnの値を変更すると、#NUMエラーが返されます。

===

どのように機能しますか?

まあ、それは非常に簡単です。それぞれの部分を一つずつ見ていきましょう。

link:/ Tips-if-condition-in-excel [IF](names =“ Rony”、 link:/ lookup-and-reference-excel-row-function [ROW](names)-` link: / lookup-and-reference-excel-row-function [ROW] `(A2)+ 1 *)

IFでは、names =“ Rony”はTRUEとFALSEの配列を返します。範囲名(A2:A10)のセルが「Rony」に一致する場合は常にTRUE。\ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}。

次の linkROW(names)- linkROW (A2)+1:リンクROW (names *):

ここで、ROW関数は、名前の各セルの行番号を返します。

\ {2; 3; 4; 5; 6; 7; 8; 9; 10}。

link:/ lookup-and-reference-excel-row-function [ROW](names)-` link:/ lookup-and-reference-excel-row-function [ROW] `(A2)*次に、行を減算します指定された配列の各値からのA2の数。これにより、0から始まるシリアル番号の配列が得られます。

\ {0; 1; 2; 3; 4; 5; 6; 7; 8}。

link:/ lookup-and-reference-excel-row-function [ROW](names)-` link:/ lookup-and-reference-excel-row-function [ROW] `(A2)+1:取得するには1から始まるシリアル番号は、この配列の各値に1を加算します。これにより、1から始まるシリアル番号が得られます。

\ {1; 2; 3; 4; 5; 6; 7; 8; 9}。

これで、IF(\ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}、\ {1; 2; 3; 4; 5; 6; 7; 8; 9})ができました。

これは\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}に解決されます。

これで、式が link:/ statistics-formulas-excel-small-function [SMALL](\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}、2)に解決されました。

これで、SMALLは範囲内で2番目に小さい値である7を返します。

どのように使用しますか?

質問が到着します:n番目の一致の生のインデックスを取得することの利点は何ですか? n番目の値から関連情報を取得できればさらに便利です。まあ、それもできます。範囲名(A2:A10)のn番目の一致の隣接セルの値から値を取得する場合。

{=INDEX(B2:B10, SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2))}

そうそう、これが範囲内でn番目の一致を取得する方法です。私は十分に説明できたと思います。この記事またはその他のExcel / VBA関連のトピックについて疑問がある場合は、以下のコメントセクションに書き込んでください。

関連記事:

link:/ information-formulas-how-to-get-sequential-row-number-in-excel [Excelで順次行番号を取得する方法]

link:/ lookup-formulas-vlookup-top-5-values-with-duplicate-values-using-index-match-in-excel [Vlookup Top 5 Values with Duplicate Values Using INDEX-MATCH in Excel]

link:/ lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]

link:/ lookup-formulas-use-index-and-match-to-lookup-value [INDEX andMATCHを使用して値を検索する]

link:/ lookup-formulas-lookup-value-with-multiple-criteria [Lookup Value with Multiple Criteria]

人気の記事:

link:/ forms-and-functions-introduction-of-vlookup-function [ExcelのVLOOKUP関数]

link:/ Tips-countif-in-microsoft-excel [COUNTIF in Excel 2016]

link:/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]