Search multiple non-contiguous columns

I have a spreadsheet I use as a database of all the resources I might use on a project – suppliers, subcontractors, consultants etc

It is a large sheet as I have all contacts for all departments within a particular company on one line with their office phone no, direct dial,cell/mobile number and email addresses and a lot more details about company registrations, taxes, insurances and other specific details.

So the columns go to from column B to HY and there are about 8500 lines of companies – which I add to regularly. I have 15 “person name” contacts per company set up each 8 columns for the particular person ie the person columns are 9 apart.

As I don’t use some of the resources for long periods I forget the details, so, I am trying to create some wildcard searches – such that I can put in a part of a name and the result will bring up a list of people, companies etc that contain the shortened details I have entered

I have used Countries and US States to illustrate the data in the attached sheet

With company name it is easy – I only have one column to search – so in the first (blue) results column (column G) if I search “AR” in the input cell D5, I get a correct list of all the US States that contain “AR” in column G. Cell D8 tells me how many results I should have – and is currently set for the “person name” based on the later search: it would read 6 in the first column results check.

However if I try and extend the search/results area to column C (to represent a much wider column search for the whole worksheet) by extending B4:B54 to B4:C54 I get an error message in the second results column in column J.

If I concatenate columns B&C and just add a random word in that contains “AR” into column C, the formula in the third results column in column M recognizes that “park” is in column C but adds in the data from Column B so the result is “Californiapark” not “park” ie the formula is recognizing something containing “AR” is in line 9, but not isolating data in the two columns on line 9 to give the correct result.

I then created the list of States, European Countries and African States in Columns S, V and Y to represent a sample of the form of the data for the “person names” in my spreadsheet (where “person names” in each company are separated by detail such as phone number and email columns following the “person name” column for each individual in a company as noted above). I tried again to concatenate the data columns and got gobbledegook as an answer – the fourth results column in column Q – the results are not separated for all three columns.

So, I wondered if creating a defined name for the multiple columns S, V and Y (that represent the “person names” columns in my spreadsheet. In my example I used the defined name “States” for the data in columns S, V and Y. Again I got gobbledegook as an answer in the 5th results column in column T - as per the previous version in column Q.

Desired results are in results column 6 - column W

It could be that the basic formula I am using cannot work with a multi-column array – but I am not proficient enough to come up with a different formula!
I just wondered if anyone had any ideas? I am sure there must be a way to do this – I just can’t find it!

Cheers!

Hey Bo_Ry

The formula works so Thank You!

The problem is that it brings up email addresses as well as names - but that is my fault as I had not specifically limited the expansion just to the "person names" column.

Glen

Yes, I figured out when I saw your first response and put Bo_Ry's solution in, that I need to isolate the "person name" column as it repeats the names from the email address column - which isn't a major issue - other than it means that if I put "rich" in for Richard, it brings up every Richard and their email address. I am limiting the search to 50 results - which means that if I add emails in that takes up some of my search results - and so might not give me the result I am searching for.

Had I had a smaller database, bringing up the email might actually have been handy as it would have told me the company name: but as I am using an index/match search for each result from this formula to brings up the company detail in the next/adjacent cell, the email advantage disappears - the advantage is in having as many results as possible.

But Bo_Ry's solution is helpful for me in that I seem to come up against a need to use very similar formulas all the time - so there will probably be another situation where I can use his formula very soon! Similarly with kvsrinivasamurthy's.

I use questions like this to learn about the different ways to use different functions/formulas - and how to apply them - so, for me, no solution that works is ever wasted - as I am seeing different ways to consider the same problem.

I will put your solution in tonight and come back later to give feed back on it

Hey Glen

Well, I got your original formula to work - which took a while given I had to edit it for the different column references etc in my actual workbook (particularly given the data and input/results are on two separate sheets in my workbook): so the original formula definitely works!

I will look at the new version tomorrow when I am a little more awake! I tend to be anxious to get a task done when I am working in excel and have stayed up very late to finish that spreadsheet! I had quite a few other searches to add to the sheet.

But one thing I wanted to ask you: in one section there is a divisor of 3 - I presume this sets the number of columns the formula is to use for one person ie to ignore the 2 columns that, as you say, contain the email addy and mobile no. I remember you asked this in your first message....

The reason I am playing with this formula is that I am in the middle of re-vamping the database sheet: originally it did just have 3 columns - and I am using the original at the moment as there are resources noted in that one that I need that are not on a later one: but the later one has other columns and is much more useful as it has direct dial numbers and other info I need in there. So, my intent is to merge the two by putting the data from the old one into the new. Then I would have 6 columns per person as I will be adding a office location, DD office number and first aider status to their columns. So, would I need to do anything else other than to change the 3 to 6 to add in the extra columns per person? I have studied the original formula (as best as my low level of proficiency allows!) and can't see any reason that there should be more to change, but thought I would check. I can now see why you made the width comment: and yes, in some cases, for office based staff for instance, I do not have the first aider column as it is not important: but to make life easier I can add it to all persons and just leave it blank - I presue when you say width, you mean no of columns per person?