Search multiple words within row

bsh3p

New Member
Joined
Jul 12, 2018
Messages
4
Hi all, first post - thanks in advance for taking the time to take a look.

I've looked and looked for an answer to my issue but no luck, hoping somebody can please help.

My file looks like below but with 65k rows.

Hotel NameLocation
Best WesternWashington
The hiltonnew york
Intercontinental the BarclayNew York
Hollywood Hiltonhollywood

<tbody>
</tbody>

I want to search for a hotel using multiple search terms and return row number or potentially highlight any row that contains all words exactly (don't want case sensitivity). Also, as I will be repeating this process I want to define a named range and enter the search criteria into the sheet rather than changing the formula each time as below.

Search Criteria
hilton
new york

<tbody>
</tbody>

As above, search terms are "hilton" and "new york" so I want to return row 3 from the data file. I do not care about rows 4 or 5 that contain only one of these values (Hilton/New York).

As in example above with "The hilton", I cannot simply use Ctrl-F as hotel names are not exact - "Hilton New York" does not appear in a single cell. As per other examples given, hotel name may or may not contain location and doesn't have a set standard! Of course :(

Using Office2010 if that helps, hopefully above makes sense.

Regards, B
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Something like...

=LOOKUP(9.99999999999999E+307,MATCH("*"&E2&"*"&"|"&E3,A:A&"|"&B:B,0))
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
Alternatively, particularly if there might be more than one match, you could do a filter :
Code:
Sub v()
[A:B].AutoFilter
[A:B].AutoFilter field:=1, Criteria1:="=*" & [E2] & "*"
[A:B].AutoFilter field:=2, Criteria1:="=*" & [E3] & "*"
End Sub
Code could be added to return whatever details required (row number(s), etc.) for the filtered result.
 

bsh3p

New Member
Joined
Jul 12, 2018
Messages
4
That worked!!! Thanks Aladin :)

I won't use the word issue, however from doing testing it seems to return the row value that comes first. Is there a way to return all row values where the combination exists? As there are many instances where there are many Hiltons in the one city for example. Perhaps given this scenario, it may be easier to do a conditional formatting formula entry to highlight all matching rows?

For my learning benefit, may I please ask what all the 9's are for in the formula?

Thanks so much!

Thanks also to footo, haven't touched coding before so Aladin's solution seemed the easiest option to implement for my skill level!

B
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks also to footo, haven't touched coding before so Aladin's solution seemed the easiest option to implement for my skill level!
All you have to do is copy the macro I posted, press Alt+F11, and paste the macro in the window on the right.
To run the macro go to Developer/Macros.
It can be assigned to a button if you wish.

The macro does all you have requested in your last post - it filters the data by the required criteria.
 
Last edited:

bsh3p

New Member
Joined
Jul 12, 2018
Messages
4

ADVERTISEMENT

Thanks footoo for the guidance, I got that to work. Easy when you know how hey!

Unfortunately, I just realised that my data is spread across 2 tabs. I assume this is a data limit as tab 1 has 65536 rows. Not sure if there's a way around this but if not, is there a way to get this macro to work still? Sooooorrry!

Ideally I guess, search criteria goes into tab 1 to search data across tabs 2 and 3. Search results then display in tab 1.

@Aladin, yes there are often multiple hotels with similar names in the same location. Most likely, when I get the search function right I can add more search criteria with more specific words or even an address to deal with this. My data is more complicated, just trying to make this simple for people to assist to get me on the right track.

eg search criteria may be "hilton" "midtown" "mid-town" "new york" "avenue" "nyc" "ave"
to give me the row corresponding to "New York Hilton Midtown" address "1335 6th Avenue, New York, NY 10019, USA"

PS I don't work for Hilton, just a nice easy to spell example :D Easier to type than some others!

Thanks again
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Try to present a representative sample along with representative search criteria and associated output.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
Unfortunately, I just realised that my data is spread across 2 tabs. I assume this is a data limit as tab 1 has 65536 rows. Not sure if there's a way around this but if not, is there a way to get this macro to work still? Sooooorrry!

Ideally I guess, search criteria goes into tab 1 to search data across tabs 2 and 3. Search results then display in tab 1.

So your data exceeds 65536 rows? (Maybe time to update to a newer Xl version!)

Before I spend time writing a macro, please confirm that is the way you want to go.
Also would need info like the name of the criteria/results sheet, the names of your data sheets, what columns used, how many header rows.

Alternatively, and hopefully, you could just run the macro separately for each sheet.
You would need to have the criteria on each sheet - but could input to one sheet and have linked formulas on the other sheet.
 

bsh3p

New Member
Joined
Jul 12, 2018
Messages
4
Thank you

I am consulting my IT department about this before I waste anybody's time!

:D
 

Forum statistics

Threads
1,136,338
Messages
5,675,187
Members
419,553
Latest member
hanahass

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
Top