Retrieve Multiple Values Horizontally Using Partial Match

[M_Leigh]

Hi All,

I know this is a fairly common topic, but I'm still having trouble.

My table:
Column A) Account ID
Column B) Account Name
Column M) Parent ID

Using partial account name, I need to retrieve all values in Column M that match. I'd like the output to populate horizontally in separate columns.

I was using =VLOOKUP(PARTIALNAME&"*",$B$2:$M$42000,12,FALSE), but this does not work when there are multiple values in M or if there is a single value not listed first in the lookup.

Thank you in advance.

 

[M_Leigh]

I've also tried =IF(COLUMN()-2<=COUNTIF($B$2:$B$42000,PARTIALNAME&"*"),INDEX($M$2:$M$42000,MATCH(PARTIALNAME&"*",$B$2:$B$42000,0)+COLUMN()-3),"")

 

[AhoyNC]

It would help if you would post a small sample of your data, but maybe something like the example below.

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down and across as needed.

Excel Workbook
	A	B	C	D	E	L	M
1	ID	Name					Parent
2	101	Sam Smith					Smith, George
3	206	Paul Jones					Jones, Sam
4	201	Pam Smith					Smith, George
5	325	Mary Smith					Smith, George
6	150	Deb Smithfield					Smithfield, Debbie
7
8		Parent
9		Smith	Sam Smith	Pam Smith	Mary Smith
10		Smithfield	Deb Smithfield
Sheet

 

[M_Leigh]

Hi AhoyNC,

Thank you for your help. Sorry, I didn't include this originally. My dataset is more like:

	A	B	C	D	E	L	M
Alex Foods
Alexander's Co					40001
Alex Corp					40002
Brian's Holdings
Brian's Corp
	Brian Farm					30201
	Alex

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: #C0C0C0"]ID[/TD]
[TD="bgcolor: #C0C0C0"]Account Name[/TD]

[TD="bgcolor: #C0C0C0"]Ultimate ID[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

[TD="bgcolor: #C0C0C0"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]

[TD="bgcolor: #FFFF00"]40001[/TD]
[TD="bgcolor: #FFFF00"]40002[/TD]
[TD="bgcolor: #FFFF00"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]

</tbody>

So just looking for M3 and M4. I'm trying to do this for a few of the columns, but I'm still stuck on M.

Thank you.

 

[M_Leigh]

Not sure why my replys (with tables) are not showing up, but thank you AhoyNC.

My table looks more like:
B1: Account Name
B2: Alex Corp
B3: Alexander's Foods
B4: Alex & Co
B5: Alex Store
B6: Brian's Shop
B7: Brian Place
M1: Parent ID
M2: No data
M3: 40001
M4: 40002
M5: No data
M6: 3321
M7: No data
Z300: Alex

I'd like to reference Z300 and return 40001 and 40002 horizontally.

 

[AhoyNC]

Maybe something like this:
Note that in the SEARCH function I added a space before the text to search. This is so it will only find words that start with say Alex and would not return a match for something like John Alex Corp.

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula across and down as needed.

Excel Workbook
	A	B	C	D	E	M
1	ID	Account Name				Ultimate ID
2		Alex Foods
3		Alexander's Co				40001
4		Alex Corp				40002
5		Brian's Holdings
6		Brian's Corp
7		Brian Farm				30201
8
9
10		Alex	40001	40002
11		Brian	30201
Sheet

 

[M_Leigh]

Maybe something like this:
Note that in the SEARCH function I added a space before the text to search. This is so it will only find words that start with say Alex and would not return a match for something like John Alex Corp.

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula across and down as needed.

	A	B	C	D	E	M
	Alex Foods
	Alexander's Co
	Alex Corp
	Brian's Holdings
	Brian's Corp
	Brian Farm
	Alex
	Brian

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:109px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:98px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Account Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Ultimate ID[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]40001[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]40002[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]30201[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]40001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]40002[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]30201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C10 {=IFERROR(INDEX($M$2:$M$7,SMALL(IF($M$2:$M$7<>"",IF(ISNUMBER(SEARCH(" "&$B10," "&$B$2:$B$7)),ROW($M$2:$M$7)-ROW($M$2)+1)),COLUMNS($C$10:C10))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks again.

I tried the formula above, but it doesn't pull any values. Does the red M2 below specifically say pull M3 and M4? Is there a way I can modify it to look up without knowing the cell locations?

{=IFERROR(INDEX($M$2:$M$7,SMALL(IF($M$2:$M$7<>"",IF(ISNUMBER(SEARCH(" "&$B10," "&$B$2:$B$7)),ROW($M$2:$M$7)-ROW($M$2)+1)),COLUMNS($C$10:C10))),"")}

 

[AhoyNC]

This part of the formula
ROW($M$2:$M$7)-ROW($M$2)+1)
is just returning an array of {1,2,3,4,etc) and the IF statement is returning a TRUE for the rows it finds the data in. You need to change the ranges to match your actual data.

In the example above this part of the formula
IF($M$2:$M$7<>"",IF(ISNUMBER(SEARCH(" "&$B10," "&$B$2:$B$7)),ROW($M$2:$M$7)-ROW($M$2)+1))
returns
{FALSE;2;3;FALSE;FALSE;FALSE}
The 2 & 3 in this case is row where it finds a match for Alex.

 

[M_Leigh]

I'm sorry. I'm still missing something here. I thought maybe it was the ISNUMBER because there are letters in some of the values in M.


https://ibb.co/f9V7em

(screenshot)

 

[AhoyNC]

Looking at your screenshot it doesn't look like the formula was entered as an array formula.
This is for a PC, (if you are on a MAC I think the you use "command-return" to enter an array formula).

In cell C10 where the formula is:
-Press F2 key for edit.
-then press CTRL-SHIFT-ENTER
If done right Excel will put {} around the formula (don't type these {} in the formula Excel will do that for you).
Then just drag formula across columns.

Excel Workbook
	A	B	C	D	E	L	M
1	ID	Account Name					Ultimate ID
2		Alex Foods
3		Alexander's Co					21001XDF5
4		Alex Corp					400002SEX500
5		Brian's Holdings
6		Brian's Corp
7		Brian Farm
8
9
10		Alex	21001XDF5	400002SEX500
Sheet