How to return column header based on a value from a table

[elf607]

Hi! I work for a non-profit agency that works with individuals with disabilities and their Service Coordinators. What I'm doing is sorting out the folks that need a letter sent as of this month, and I need also to send a copy to their SC but only the agency name shows in my sorted information, not the individual SC name. I'd like Excel to fill in the SC by looking up the client's name from a multiple-page workbook and have the column header (which is their service coordinator's name) returned.

I'm fairly new to Excel, but the array here: http://www.excelforum.com/excel-gene...m-a-table.html looks close to what I need to do.

Can anyone help me adjust it to work for me? I have three pages in the workbook, with different numbers of columns and rows per page. I'd like it to automatically fill them in, so I wouldn't have to type in each name. Is this possible? Thank you in advance.

[adm.patrikbpl]

please post sample excel file.

[6StringJazzer]

A solution was provided in the thread you linked. If that solution doesn't work for you, it means that your situation is significantly different. Therefore we need to see your actual file to be able to provide a formula. Please attach your file. If it is private data, feel free to delete the data but we need see how everything is organized. The best thing would be to replace real data with fake data, but I understand that might be very time consuming in some situations.

To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[elf607]

Thank you for answering so quickly. I've attached a dummy version of the file. The actual file has many more individuals listed (430 or so), but the layout is the same. I receive the Excel file from the department manager whenever it gets updated due to staffing changes, so I don't have any control over its layout, unfortunately. The listing of letters that need to go out is in Excel, with the agency name (not the Service Coordinator name) listed for each individual. I've been manually flipping back and forth, searching for the individual using the "Find" function, then typing the SC name in. I'm hoping there's a way to have Excel do it automatically.

[6StringJazzer]

There seems to be a missing link

I'd like Excel to fill in the SC by looking up the client's name from a multiple-page workbook and have the column header (which is their service coordinator's name) returned.

Where do you want to do this? Where is the list of client names for which you need agencies filled in? I added a sheet to try to guess what result you want. I also assume there are no other sheets in the file.

It looks like you are only concerned with the odd-numbered columns (A, C, E...). The even-numbered columns have some other data that doesn't seem relevant.

I could not think of a way to do this with formulas because the sheets and columns can vary, so I provided a macro solution.

P.S. I would like to meet with your client Jack Daniels.

[elf607]

OMG! That is awesome! I'm out of the office on Wednesday, but I'm pretty sure this is the answer I was looking for. I'll doulbe-check on Thursday, and let you know.

You are terrific; thank you for all your time and patience, and I'll let Jack know you'd like to set up that meeting.

[6StringJazzer]

One more thing--the client name you put in that new sheet has to be an exact match for how it is listed in the other sheets. If that is not going to be the case, there is more work to do.

[elf607]

I searched for a total of 225 and it found 109 of them; apparently due to names not being exactly identical in both listings. In the new SC workbook, they added some notations after the names as placeholders. It's a pain, but a small one--I only have to scrub the MSC listing once, when I get the new one, and then the search should be okay. Anyway, there are a lot fewer left for me to search for manually and after all, I was doing them all manually before you worked this miracle! Thank you again. I wish I could return the favor.

[6StringJazzer]

It is possible to look for close matches if you can tell me about how these notations work. For example, if you want to search for the name

Smith, Joe

and someone added notations like

Smith, Joe (15)

then the code could be updated to match anything that starts with "Smith, Joe". It's not that hard if you want me to try it.

[elf607]

Oh, wow! That would be really great! And you're right--the notations are (PCSS) or (KL) or * or whatever the Departmental Director uses (I have no idea what they mean )...I've attached a file with samples of the kind of thing that's been added. Thank you again for all your help.

P.S. "It's not that hard"????---to me, this is like magic. I have no idea how to do this, and I'm thrilled that the Great and Powerful Oz is here to help.

[6StringJazzer]

Here is an update that if you give it a name, it will find a match with that same name followed by any annotations.

[elf607]

I don't know how to thank you for all your time, knowledge, and patience. This is perfect! It found everybody, and you have just made my life infinitely easier. Again, my sincere thanks and gratitude for all you've done.

[6StringJazzer]

Glad to help!