Multiple Match Small Lookup with partial text

SimonThom

New Member
Joined
Feb 13, 2017
Messages
12
Hi,

I have the following formula

Code:
 =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
Code:
 SMALL(IF($A$2&"*"=Data!$C$2:$C$50
but it didn't work :(
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 13, 2017
Messages
12
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Feb 13, 2017
Messages
12

ADVERTISEMENT

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)
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Feb 13, 2017
Messages
12

ADVERTISEMENT

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.
 
Last edited:

SimonThom

New Member
Joined
Feb 13, 2017
Messages
12
This is what I am trying to achieve. Column B is the column I am trying to populate using this formula.

ABC
SimonHP Z4 G4Linux
HP Z440Windows
DavidDell T1350Linux
HP Z440Windows

<tbody>
</tbody>


This is the Data Sheet:

ABCD
Hostname1OperationalsimonHP Z4 G4
Hostname2Operationalsimon2HP Z440
Hostname3OperationaldavidDell T1350
Hostname4Operationaldavid2HP Z440

<tbody>
</tbody>
 

majidsiddique

Board Regular
Joined
Oct 22, 2018
Messages
164
Hi SimonThom,
in this scenario Simon and Simon2 are diffrent name. you want to merge both in one.
 

SimonThom

New Member
Joined
Feb 13, 2017
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,576
Messages
5,637,180
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top