INDEX MATCH Multiple Output with Partial Match

Hi everyone,

I was working on a spreadsheet where I was able to match a user input to a table and output multiple values.
If the user types in a name, it will output the value that is in an adjacent column. The name may occur multiple times, and I was able to resolve this using the SMALL(IF()) function nested within an INDEX MATCH.

My next step was my attempt to use wildcards within the array and this did not work. In the example if I type in "Illi ", hopefully I was trying to have the function output 7, 9, 10. These outputs correspond to an input of "Illinois X".

I tried things such as "*" & J16 & "*" inside my original function as well as some new functions but none seem to work. Thank you for any help!

Ray

try this one.......

Ray,
Minor typo in your formula: should be B not D

=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($J$16,$D$3:$D$12)),FALSE,TRUE),ROW($D$3:$D$12)-2),ROW(1:1))),"",INDEX($B$3:$B$12,SMALL(IF(IF(ISERROR(SEARCH($J$16,$D$3:$D$12)),FALSE,TRUE),ROW($D$3:$D$12)-2),ROW(1:1))))

Thank you for the help!