Excel lookup for to return multiple approximate matches

Hi all, I'm looking for a solution to get multiple approximate matches.

For example,
In my first workbook, I have a row with these names

BigMouth
BigMouth AG
BigMou



In my 2nd workbook,
I have BigMouth

And I want BigMouth, BigMouth AG, and BigMou to appear in the subsequent columns of my BigMouth row



Thanks,

Not sure if it will cover all your possibilities, but try this.

In Sheet1 beside the multiple entries enter a formula like:

Please Login or Register  to view this content.

Where Sheet2!A1 holds your search term "BigMouth" and A2 is the first cell on your active sheet to check.

copy the formula down. This finds all the terms that "might match".

Then in Sheet2, in say B1 enter:

Please Login or Register  to view this content.

where Sheet1, column B is where you entered the first formula above.

then in the location where you want your list entered, enter formula:

Please Login or Register  to view this content.

copied down as far as you want... adjusting again the references to suit.

Attached is a sample.

Change the last formula to:

Please Login or Register  to view this content.

copied across.

The solution was designed based on retrieving information only for 1 input...

For multiple inputs, the design is a bit different. See attached.

Added 2 helper columns in Sheet1.

In B2:

Please Login or Register  to view this content.

copied down. This does a 2-way check and finds the matching input, if any.

In B3:

Please Login or Register  to view this content.

copied down. This counts the number of matches for each input.

In Sheet2, B1:

Please Login or Register  to view this content.

copied down. This gives count of each input found in Sheet1.

in Sheet2, D1:

Please Login or Register  to view this content.

copied down and across. This extracts the matches in respective rows.