String Search & Listing Based on a Substring

[manojrke]

Hi Gurus !

I have a big database of Employees that has a unique key "ID No." now What I want is that if I enter 'Jon' in a cell then the I should be able to find all employees who have substring Jon in their names, and their names & ID no. should appear as a list.

The expected result is something like this:


Enter Full/Part Name - Jon

ID No. Name
12345 Jonathan
12347 Jonarch
13596 Jonark
19856 Jonam
95689 Jonace

How can it be done ?

Thanks in anticipation.

 

[Aladin Akyurek]

E1: Jon

A:B houses Employees from row 2 on, with headers in row 1.

E2, just enter:

=COUNTIFS(INDEX(Employees,0,2),"*"&E1&"*")

E4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$4:E4)<=$E$2,
    SMALL(IF(ISNUMBER(SEARCH("|"&$E$1,"|"&INDEX(Employees,0,2))),
    ROW(Employees)-ROW(INDEX(Employees,1,1))+1),ROWS($E$4:E4)),"")

F4, just enter, copy across, and down:

=IF($E4="","",INDEX(Employees,$E4,COLUMNS($F4:F$4)))