Multiple Match Small Lookup with partial text

[SimonThom]

Hi,

I have the following formula

 =IFERROR(INDEX(Data!$D$2:$D$100, SMALL(IF($A$2=Data!$C$2:$C$50, ROW(Data!$C$2:$C$50)-ROW($A$2)+1), ROW(1:1))),"" )

Which indexes the D column in Data tab and then performs a Small Array on the C column of Data which then displays the corresponding data from the D column in Data IF it matches what is in A2 of the main page.

Only issue I have is making A2 a partial match as it is a person's first name and the C column in Data is their username (which they have multiple of and is appended with a number) e.g.

A2 = Simon
Data!C2 = simon
Data!C3 = simon2
Data!D2 = Linux
Data!D3 = Windows

Currently it only displays Linux, unless I change A2 to Simon2.
I want it to list both.
I've tried

 SMALL(IF($A$2&"*"=Data!$C$2:$C$50

but it didn't work

 

[Aladin Akyurek]

Thanks for the input and the ouput…

Book1
	A	B	C	D
1
2	Hostname1	Operational	simon	HP Z4 G4
3	Hostname2	Operational	simon2	HP Z440
4	Hostname3	Operational	david	Dell T1350
5	Hostname4	Operational	david2	HP Z440
6
Data

Book1
	A	B	C
1	Simon	Simon	HP Z4 G4
2	Simon		HP Z440
3	David	David	Dell T1350
4	David		HP Z440
Sheet2

In A1 enter and copy down:

=IF(B1="",LOOKUP(REPT("z",255),$B$1:B1),B1)

In C1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Data!$D$2:$D$100, SMALL(IF(ISNUMBER(SEARCH($A1,Data!$C$2:$C$50)), ROW(Data!$C$2:$C$50)-ROW($A$2)+1),COUNTIFS($A$1:A1,A1))),"")

 

[SimonThom]

Ah so I have to have a name reference for every line as opposed to the single point of ref.
I was trying to do it in a cleaner way but if it works, it works I'll just hide column A once complete.

Thank you so much for this guys!

 

[SimonThom]

Spoke too soon, appears it doesn't pick up the right details for every single entry..
Will take a look myself to see where it breaks and if I get lost will update thread.

 

[SimonThom]

Spoke too soon, appears it doesn't pick up the right details for every single entry..
Will take a look myself to see where it breaks and if I get lost will update thread.

Ignore, I was stupid and didn't expand the array passed 50 lines

 

[Aladin Akyurek]

Ah so I have to have a name reference for every line as opposed to the single point of ref.
I was trying to do it in a cleaner way but if it works, it works I'll just hide column A once complete.

Thank you so much for this guys!

You are welcome. Glad to help.