Lookup string (with wildcard) from list and return multiple values

[MichaelS94]

Hi,

I'm trying to use the index and small functions (from a formula i found online) to search a list of text and return any values that have a partial match.

E.g. I search for "book" and it returns a corresponding number for "antique books", "assembling of books", "book publishing" etc.

The formula i used is below and i found it online - it doen'st work with wiildcards and only works if there is an exact match.

=IF(ISERROR(INDEX($A$1:$B$15600,SMALL(IF($A$1:$A$15600=$E$1,ROW($A$1:$A$15600)),ROW(1:1)),2)),"",INDEX($A$1:$B$15600,SMALL(IF($A$1:$A156008=$E$1,ROW($A$1:$A$15600)),ROW(1:1)),2))

Here is what my spreadsheet looks like and ideally how I want it to work:

List	Number	Lookup	Book
bla bla	1	Matches:	4
bla bla	2		7
bla bla	3		11
book maker	4
bla bla	5
bla bla	6
antique books	7
bla bla	8
bla bla	9
bla bla	10
book publisher	11
bla bla	12
bla bla	13
bla bla	14
bla bla	15

<tbody>
</tbody>

Could any of you please have a look and offer any recommendations /advice? Essentially I just have a long list and want to be able to search for something and it would return any (often multiple) suitable matches.

Thanks so much - really stuck on this one!

 

[Aladin Akyurek]

What is the name of the sheet housing the data? Those numbers are sequential: is this on purpose or unfortunate example?

 

[Peter_SSs]

Try this, copied down, provided you are using Excel 2010 or later.

Excel Workbook
	A	B	C	D
1	List	Number	Lookup	Book
2	bla bla	1	Matches:	4
3	bla bla	2		7
4	bla bla	3		11
5	book maker	4
6	bla bla	5
7	bla bla	6
8	antique books	7
9	bla bla	8
10	bla bla	9
11	bla bla	10
12	book publisher	11
13	bla bla	12
14	bla bla	13
15	bla bla	14
16	bla bla	15
17
Partial match

 

[MichaelS94]

Spreadsheet Formulas

Cell Formula D2 =IFERROR(AGGREGATE(15,6,B2:B16/(SEARCH("*"&D$1&"*",A2:A16)>0),ROWS(D$2:D2)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

That works perfectly! Thank you so much!

One final addition (if possible) - would you be able to set it so that you can input two words (e.g. "book maker") into the lookup field? In other words you could search for multiple words and it would return values that match all of the words?

 

[MichaelS94]

Spreadsheet Formulas

Cell Formula D2 =IFERROR(AGGREGATE(15,6,B2:B16/(SEARCH("*"&D$1&"*",A2:A16)>0),ROWS(D$2:D2)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

That works perfectly! Thank you so much!

Two additional things:

1. Is it possible to change the formula to return the actual matched text rather than the corresponding number? So you search for "book" and it returns "book maker", "antique books" etc. rather than "4", "7" and "11" etc.?

2. would you be able to set it so that you can input two words (e.g. "book maker") into the lookup field? In other words you could search for multiple words and it would return values that match all of the words? (Don't worry if not possible - I'm just curious as to if it would work)

Thank you!

 

[Aladin Akyurek]

That works perfectly! Thank you so much!

Two additional things:

1. Is it possible to change the formula to return the actual matched text rather than the corresponding number? So you search for "book" and it returns "book maker", "antique books" etc. rather than "4", "7" and "11" etc.?

2. would you be able to set it so that you can input two words (e.g. "book maker") into the lookup field? In other words you could search for multiple words and it would return values that match all of the words? (Don't worry if not possible - I'm just curious as to if it would work)

Thank you!

Are those values in B2:B16 sequential or just numbers? Are they even part of you data, considering that they are not needed for calculating 4, 7, 11?

 

[Peter_SSs]

Also, can you clarify the following

.. would you be able to set it so that you can input two words (e.g. "book maker") into the lookup field? In other words you could search for multiple words and it would return values that match all of the words?

For example. if "book maker" was in the lookup cell, what should be returned from this list?

Excel Workbook
	A	B	C	D
1	List	Number	Lookup	book maker
2	I read a book	1
3	He is a home-maker	2
4	Ask the book maker	3
5	Ask the maker of the book	4
6	Have you seen a boobook owl?	5
7	Tom Makerson was at the booklaunch	6
8	Did you read the book Makerson wrote?	7
Partial Search

 

[MichaelS94]

Also, can you clarify the followingFor example. if "book maker" was in the lookup cell, what should be returned from this list?

Partial Search

*	A	B	C	D
1	List	Number	Lookup	book maker
2	I read a book	1	*	*
3	He is a home-maker	2	*	*
4	Ask the book maker	3	*	*
5	Ask the maker of the book	4	*	*
6	Have you seen a boobook owl?	5	*	*
7	Tom Makerson was at the booklaunch	6	*	*
8	Did you read the book Makerson wrote?	7	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:262px;"><col style="width:70px;"><col style="width:67px;"><col style="width:97px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

So each value in the list has a corresponding identification number (which I simplified here to be 1,2,3 etc. for the example). I decided it might be easier to ignore these ID numbers for now, and just use a VLOOKUP later once the suitable match has been selected.

Ultimately, I want the end user to be able to search for something (e.g. "book maker") then have a list of possible matches returned (e.g. ask the book maker, ask the maker of the book), from which the end user can manually decide which is the best match. The VLOOKUP would then tell the end user what the ID code for this is.

To give some context, these ID codes are used to label projects based on their specific sector/industry. So when someone is working on a project, they need to decide what ID code to give it, and say the project is related to book making, I'm hoping this spreadsheet could return a smaller selection of possible IDs to choose from rather than having to manually search through a list of 16000 values!

Thanks so much, really appreciate your willingness to help!

 

[Peter_SSs]

Sorry, I wasn't asking for a general description of what you are trying to do, I was asking specifically which values from the list I provided, you would have wanted returned if "book maker" was in the "Lookup box".

Every one of the items in my list contained either "book", "maker" or both but in varying order and in varying combinations with other text.

Does your response below mean that the only two you items you would have wanted returned from my list were the two coloured items? That is, you want items with both lookup words and those words must not be combined into a longer word like "boobook" but the words can be in any order?

Does the below respons

.. search for something (e.g. "book maker") then have a list of possible matches returned (e.g. ask the book maker, ask the maker of the book),

 

[MichaelS94]

Does your response below mean that the only two you items you would have wanted returned from my list were the two coloured items? That is, you want items with both lookup words and those words must not be combined into a longer word like "boobook" but the words can be in any order?

Does the below respons

Ah sorry, misunderstood you.

But yes, that's exactly right. The words could appear in any order and should not be combined into a longer word. For example, if I search "road" I don't want "broadcast" etc. appearing.