Kimmには、3つの列があるワークシートがあります。列Aには個人の姓が含まれ、列Bには名が含まれ、列Cには個人の電話番号が含まれます。キムがその人の姓名を知っている場合（それぞれセルF1とF2にあると言う）、彼女はその姓名に一致する最初の人の電話番号を返すためにどのような検索式を使用するのか疑問に思います。

実際には、電話番号を把握するために使用できるいくつかの異なる式があります。ほとんどのアプローチでは、Ctrl + Shift + Enterを使用して常にセルに入力される配列数式を使用します。数式を理解しやすくするために、名前付き範囲を操作するのが最善です。たとえば、次の名前を設定します。

  • 列Aですべての姓を選択し、LNamesなどの名前を付けます。

  • 列Bですべての名を選択し、FNamesなどの名前を付けます。

  • 列Cですべての電話番号を選択し、電話などの名前を付けます。

電話番号を見つけるために使用できるさまざまな配列数式は次のとおりです。

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)

=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))

=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))

=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)

さらに、次に示すように、ROW関数に依存する配列数式を作成できます。

=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))

=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))

このアプローチを使用する場合（ROW関数に依存）、実際のデータの前に表示される行数を反映するように、数式の「-1」部分を調整する必要がある場合があります。この場合、データテーブルの最初の行は各列のヘッダーで構成されているため、行は1つ減ります。データ自体は2行目から始まります。

これまでに使用されたすべての式は、比較を行うために姓と名の組み合わせに依存していることに注意してください。これにより、場合によっては「誤検知」が発生する可能性があります。たとえば、データに含まれている2つの名前がThomAstonfieldとThomasTonfieldであるとします。これらの式では、名前の文字の大文字と小文字は関係ないため、これらの個人の姓名を組み合わせると、まったく同じになります。したがって、Thomas Tonfieldの電話番号を探していて、彼の名前がThom Astonfieldの後にリストに表示されている場合は、Thomasの代わりにThomの電話番号が常に表示されます。

この潜在的な問題を回避するには、姓と名の間に何らかの区切り文字を含めることができます。ヒントの前半の式の1つを使用して、次に示すように、調整を行うだけです。

=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))

姓と名の間にコロンを追加すると、区切り文字として機能し、誤検知の可能性が排除されます。

また、セルF1とF2にデータにまったく表示されない名前が含まれている場合、数式から返される情報に誤りがあることにも注意してください。使用する関数のバリエーションによっては、実際のエラー状態（＃N / Aや#REFなど）が返される場合があります

または、明らかに間違ったデータが返される可能性があります。エラー状態が返される場合は、次のように、一致するものが見つからない可能性を考慮して数式を調整することをお勧めします。

=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))), "no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))

これらのタイプの数式を使用する際に留意すべき点がいくつかあります。何よりもまず、返される情報の信頼性は、データリスト内の情報の品質に大きく依存します。データにスペルミスがある場合、空白が含まれている場合、奇妙な順序で並べ替えられている場合、または同じ人物のエントリが複数ある場合は、数式が返す内容に影響を与える可能性があります。

データの品質がよくわからない場合は、数式ではなく、Excelのフィルタリング機能を使用することをお勧めします。オートフィルターを適用すると、データの最初の2列を使用して、姓名を選択できます。これにより、選択した個人の電話番号が返されます。実行は非常に簡単で、必要なデータを簡単に選択できます。

最後に、問題に対処するために使用できる他のアプローチがあることを理解する必要があります。たとえば、DGET関数を使用する数式を作成できますが、その場合は、ワークシートまたはブックに小さな基準テーブルを追加する必要があります。 Kimmは、ワークシートに中間結果を追加できないと指定したため、基準テーブルの追加が必要になるため、ソリューションとしてDGET関数を含めないという編集上の決定が行われました。

さらに、マクロの使用に慣れている場合は、データを調べて要求された電話番号を返すユーザー定義関数を作成することもできます。このようなアプローチを使用する利点は、関数が実際に返す情報をより柔軟に処理できることです。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（10479）は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice 365のExcelに適用されます。Excelの古いメニューインターフェイス用のこのヒントのバージョンは、次の場所にあります。

link：/ excel-Pulling_a_Phone_Number_with_a_Known_First_and_Last_Name [既知の姓名で電話番号をプルする]。