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

 

[steve the fish]

Assuming your first formula is working and that the number is always appended to the end of the name then try this slight change:

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

 

[SimonThom]

Thanks Steve, that works until I change the $A$2 to $A$5 for the next username after Simon then returns the wrong details.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

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

 

[SimonThom]

Control+shift+enter, not just enter, and copy down:

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

This displays the wrong data. Every value returned is the same (The very first match)

 

[Aladin Akyurek]

This displays the wrong data. Every value returned is the same (The very first match)

Change $A$2 to $A2. Is that what you want? If not, please try to post a small sample along with the expected output for that sample.

 

[SimonThom]

Change $A$2 to $A2. Is that what you want? If not, please try to post a small sample along with the expected output for that sample.

It has to remain $A$X as the name is located in that single cell.
I'll try to explain further below.

 

[SimonThom]

This is what I am trying to achieve. Column B is the column I am trying to populate using this formula.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Simon[/TD]
[TD]HP Z4 G4[/TD]
[TD]Linux[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HP Z440[/TD]
[TD]Windows[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Dell T1350[/TD]
[TD]Linux[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HP Z440[/TD]
[TD]Windows[/TD]
[/TR]
</tbody>[/TABLE]


This is the Data Sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Hostname1[/TD]
[TD]Operational[/TD]
[TD]simon[/TD]
[TD]HP Z4 G4[/TD]
[/TR]
[TR]
[TD]Hostname2[/TD]
[TD]Operational[/TD]
[TD]simon2[/TD]
[TD]HP Z440[/TD]
[/TR]
[TR]
[TD]Hostname3[/TD]
[TD]Operational[/TD]
[TD]david[/TD]
[TD]Dell T1350[/TD]
[/TR]
[TR]
[TD]Hostname4[/TD]
[TD]Operational[/TD]
[TD]david2[/TD]
[TD]HP Z440[/TD]
[/TR]
</tbody>[/TABLE]

 

[majidsiddique]

Hi SimonThom,
in this scenario Simon and Simon2 are diffrent name. you want to merge both in one.

 

[SimonThom]

Hi SimonThom,
in this scenario Simon and Simon2 are diffrent name. you want to merge both in one.

Same user, different alias used.
If you can just do a partial match for the name minus the number then it should be achievable no?
It's easy enough to do in a bash / python script within a loop but I can't seem to get it nailed within Excel.