Retrieve Multiple Values Horizontally Using Partial Match

M_Leigh

New Member
Joined
Dec 17, 2017
Messages
7
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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),"")
 
Upvote 0
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
ABCDELM
1IDNameParent
2101Sam SmithSmith, George
3206Paul JonesJones, Sam
4201Pam SmithSmith, George
5325Mary SmithSmith, George
6150Deb SmithfieldSmithfield, Debbie
7
8Parent
9SmithSam SmithPam SmithMary Smith
10SmithfieldDeb Smithfield
Sheet
 
Upvote 0
Hi AhoyNC,

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

ABCDELM
1IDAccount NameUltimate ID
2Alex Foods
3Alexander's Co40001
4Alex Corp40002
5Brian's Holdings
6Brian's Corp
7Brian Farm30201
8
9Alex4000140002
10

<tbody>
</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.
 
Upvote 0
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.
 
Upvote 0
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
ABCDEM
1IDAccount NameUltimate ID
2Alex Foods
3Alexander's Co40001
4Alex Corp40002
5Brian's Holdings
6Brian's Corp
7Brian Farm30201
8
9
10Alex4000140002
11Brian30201
Sheet
 
Upvote 0
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.

ABCDEM
1IDAccount 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 Alex4000140002
11 Brian30201

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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))),"")}
 
Upvote 0
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.
 
Upvote 0
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
ABCDELM
1IDAccount NameUltimate ID
2Alex Foods
3Alexander's Co21001XDF5
4Alex Corp400002SEX500
5Brian's Holdings
6Brian's Corp
7Brian Farm
8
9
10Alex21001XDF5400002SEX500 
Sheet
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top