Search & Match Functions return unexpected results

I am trying to create a search engine that will search the "A" column in all sheets in my workbook. Kind of like how a filter would do it for instance if I put in the search box "plane" it should find the word "airplane" etc...I can't use VBA, since this needs to work on my Android phone's Excel app. I also do not know of an Access app to work for this either.

I think I could rig this up if I could get a function that would return the right results like I described above. VLOOKUP approximate match apparently doesn't do this correctly either from my Google search and testing. So I have tried functions MATCH and SEARCH functions. I am attaching an example spreadsheet that searches a short list, so you can see what I am talking about. It's like sometimes Search will return the right results and other times MATCH will, but neither is consistent.

I appreciate any help you guys have to offer. I have a decent amount of programming experience, but still new to Excel Magic.

Try this ...

=MATCH("*"&B2&"*", $C$4:$C$6,0)

Or:

=VLOOKUP("*"&B2&"*",C4:C6,1,0)

Thanks guys! This was what I was looking for. Don't know why I didn't think of trying wildcards, but that did work for the question in this post. I guess I assumed
Exact Match in VLOOKUP meant that literally and Approximate Match meant just that. While I have everyone's attention on this thread. Is there a way to return a list
of matches. An example would be great, but if could you at least point me in the right direction that awesome too.

Originally Posted by MrShorty

I generally feel like the easiest way to return a list of matches is to use the built in filter tools (http://www.wikihow.com/Use-AutoFilter-in-MS-Excel ). The user would pull up the filter drop down, select "text filters" -> "contains" -> and enter the desired text string in the dialog.

Yes, I already have it in a table where I can filter it. But the problem is that they are in separate sheets within the workbook and I don't want to have to go through each individual sheet to see if it is this one or that one every time I want to search for something. I wouldn't be opposed to if there was a way to some how concatenate all the sheets into one if it could be done automatically where I wouldn't have to copy and paste every time I made a change. As the columns are consistent in every sheet, but I still need the sheets separated so items are grouped together. If there is a better way of doing this I open to suggestions.

Originally Posted by ilikerolls

Is there a way to return a list
of matches.

I don't know if this is a perfect fit for what you're looking for, but you can approximate a "Search" function via formula by using the following formula, array-entered with Ctrl + Shift + Enter instead of the regular Enter, then filled down:

=IFERROR(INDEX($C$4:$C$11,SMALL(IF(ISNUMBER(SEARCH("*"&$B$2&"*",$C$4:$C$11)),ROW($C$4:$C$11)),ROW(1:1))-ROW($C$4)+1),"")

Fill it down through as many cells as you think you'll need. It should auto-update as you change your search term in B2.

If you want to search through ranges in multiple sheets, the easiest (formula) approach might be to use several versions of the formula above in columns alongside each other, but with each column tied to a different search range, so F4 and down returns matches from table 1, G4 and down returns matches from table 2, etc.

Take a look at the attachment to see if it proves helpful.

Originally Posted by CAntosh

I don't know if this is a perfect fit for what you're looking for, but you can approximate a "Search" function via formula by using the following formula, array-entered with Ctrl + Shift + Enter instead of the regular Enter, then filled down:

=IFERROR(INDEX($C$4:$C$11,SMALL(IF(ISNUMBER(SEARCH("*"&$B$2&"*",$C$4:$C$11)),ROW($C$4:$C$11)),ROW(1:1))-ROW($C$4)+1),"")

Wow this is the closest thing to what I was looking for thanks for sharing. I was able to apply this to my Excel sheet and return multiple matches as you described. Although I am doing something wrong here when applying this to my worksheet. For instance I type in the word "plane" and the correct results like "airplane" show up, but then something like "Air Conditioner" is in the list?? Or I type in "head" and "hammer" pops up, but not "headache"? Any clue as to why this happens? I tried adding the word "Air Conditioner" to your example and works as it should(doesn't show up when I type in "plane"). Do I need to format my search column in a certain way? My data is in Tables if that makes a difference? Right now it is set to "General". Should it be set to "Text"?

If you want to search through ranges in multiple sheets, the easiest (formula) approach might be to use several versions of the formula above in columns alongside each other, but with each column tied to a different search range, so F4 and down returns matches from table 1, G4 and down returns matches from table 2, etc.

Yeah this is kind of what I expected I would have to do in Excel, which I am OK with.

It sometimes seems to me that these kinds of question are easiest (long term) if you can compile everything into one, good database, then build queries and pivot tables and such off that database.

You make a good point Mr. Shorty. I do have some database experience fortunately with MySQL, Oracle, and DB2. I can't imagine MS Access being that difficult. I will take a look at this. I wouldn't mind using CAntosh's solution in the immediate short term though and I am curious what I am doing wrong with making this Search function not returning the expected results.

When you get the mismatches on your sheet, do you notice that the "wrong" returns are always the same number of rows above/below a correct match? Without seeing your sheet, I would guess that the "-ROW($C$4)+1" part of the formula is the issue. You want this portion of the formula to always equal one less that the row that the your data list starts in. In the sample I attached, the data is in C4:C11, so we needed that portion to subtract 3, so -ROW(C4) =-4, plus one gets us to -3. My guess would be that the data range on the problematic sheet doesn't start on row 4, so the formula needs to be adjusted. I hope that makes sense?