Search for value in multiple columns, Return Column Headers

Overview of Project: I have many different tables that all have different columns in them. Some columns may occur on different tables. All the data is "linked" and the only way to do that is to find a common occurance. For example "table 1" may have date member joined, name of member, and member ID. "Table 2" may have member name and hair color. If I want to see all of these fields Date, Name, Hair Color I will need to use both these tables.The data is organized as such:

Table 1_______Table 2_______ Table 3_______ Table 4
Date _________Name_________ Hair Color_____ Name
Name ________Hair Color______ Shoe Size_____ Height
Member ID____________________________________

So my current question is that I want to be able to search for a value and have excel return ALL columns headers that have this value. For example: search value "Name", excel returns "Table 1, Table 2". Currently I am using the code (headers on A2:BD2 and values on A3:BD118) =INDEX(A2:BD2,MAX(INDEX((A3:BD118=BF10)*COLUMN(A3:BD118),)) ). This works great except that it only returns the last column header that the value was found in. I want it to return EVERY column the value is found in.

.....

Eventually I want to be able to type in 2 or 3 values and have excel tell me the MINIMUM tables needed to have all these information but I think this is the first step. Eventually I would want to be able to say that I want "Name", "Date", "Hair Color" and have excel return "Table 1, Table 2" (because these are the two tables needed). NOTE: I don't want it to return Table 1, Table 2, Table 4 because I want the minimum amount of tables necessary.

I know that was probably very confusing. Let me know if I can clear this up. Please help. Thanks

Hi Zach,

No, it's not too confusing but it would still help a lot, and possibly save me a lot of time, if you uploaded a small version of your spreadsheet, show how and where the tables are arranged, what sheets they are on, (if any), and how you want the values returned to you, etc.

Later this evening, when I get home, assuming no one else has already helped you, I can write this code for you.

So basically (as a small scale example) if you look at columns A and C (titled ADJ_HCS and ADJ_HCS_FACIL_KEY respectively) you will notice that each column contains a cell that says "HCS_KEY" (NOTE: There are more of these on this table but this is just a small scale example). So basically I want to be able to type in HCS_KEY and have excel return "ADJ_HCS" AND "ADJ_HCS_FACIL_KEY". When I wrote code for it, I could only get it to return the most recent column header it was found in. But I want it to return all column headers that contain it. I don't know where it is easier to write it as a formula where it searches a input cell (where I type what I want to find) and then the formula cell spits out the output. I'm guessing its not. I'm guessing it will be easier to make it as a vba macro that reads the input cell (thats what I had tried doing anyway) and return the output. Does this clear things up?

And like I said this is just the first step for me. Eventually I'd like to be able to choose multiple variables (HCS_KEY, MBR_DATE, NPI) and have excel give me the least amount of tables (column headers) needed to return those variable. I THINK this is the first step towards achieving that goal for me. I think this will make more sense once you see the actual setup.

This then, is the first, first step. It seems to search your data as desired, with the simple tests I did.
The user is prompted for a search string, and each column is searched for that string.
When the search is over, a message box appears, giving the results.

Please Login or Register  to view this content.

Great! This does return the results I want! Now is there someway I can modify it so that 1) The output is put into a cell (or possibly multiple cells - a cell per column found) rather than in an output box and 2) Modify it so that rather than returning column A,c,etc it returns the column name? Thanks you have been very helpful!