Search Table by Partial String Criteria, No Arrays

[daffodil11]

Hello,

I have a huge Crystal Report that I use to feed one of my quarterly forms. The name fields I am searching often contain middle initials, middle names, prefixes or suffixes. Our compensation roster searches each string for matches to first and last name, and then reports corresponding data offset a number of a rows and columns. (Crystal Reports don't covert well to Excel files and always injects 5-10 rows and 5-10 columns per statistic.)

For the purpose of whom I'm handing this off to, I need to find a way to generate the data without using arrays.

Assuming A:A has strings, B:B has values, can I retrieve values of B where C and D both appear within A?

index-search no array.xlsx

[benishiryo]

hi daffodil11. assuming your numbers are always the 3rd row after the Names, try this in I2:
=INDEX($C$4:$C$14,MATCH("*"&H2&"*"&G2&"*",$A$2:$A$12,0))

if the names are unique, this will suffice:
=SUMIF($A$2:$A$10,"*"&H2&"*"&G2&"*",$C$4:$C$12)

change the range to column E for Score2

[kvsrinivasamurthy]

Pl see the attached file.

[daffodil11]

Many thanks, both of you. This has been an issue for nearly 8 months.

I can finally put this issue to bed and delegate the assignment.