Formula question-Does anyone have any ideas on a formula that could do this?

[Amanda]

I have a worksheet with client names in multiple columns. I want to be able
to have a cell where someone can enter a last name into it, and the formula
will return all of the company names (which is in my column A of every
record) for each record the last name is found. Does anyone have any ideas
on a formula that could do this? Thank you so much for any time spent on
helping me.
--
Amanda

[comotoman]

I have a similiar setup right now, but in order for it to work, you need to device a form of grouping. The way I have to set it up was Clients Names, Last, First. Then all the companys they are associated with. That would enable you to select the name in a drop down list, in another cell of your choice a drop down list would come up with all the companys names for that person. Another more complicated way I cant explain, it envolves offset, match, and lookup formulas. But it would enable you to type all or part of the name and the companys will show in a set amount of cells. Again, grouping is key. Let me know what intrest you the most and I may be able to get you going.

[Amanda]

Thank you very much for your prompt reply to my question. The second option
interests me more. I don't understand what you mean by grouping.

Thank you.
--
Amanda


"comotoman" wrote:

>
> I have a similiar setup right now, but in order for it to work, you need
> to device a form of grouping. The way I have to set it up was Clients
> Names, Last, First. Then all the companys they are associated with.
> That would enable you to select the name in a drop down list, in
> another cell of your choice a drop down list would come up with all the
> companys names for that person. Another more complicated way I cant
> explain, it envolves offset, match, and lookup formulas. But it would
> enable you to type all or part of the name and the companys will show
> in a set amount of cells. Again, grouping is key. Let me know what
> intrest you the most and I may be able to get you going.
>
>
> --
> comotoman
> ------------------------------------------------------------------------
> comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
> View this thread: http://www.excelforum.com/showthread...hreadid=503364
>
>

[comotoman]

In order for excel to pull up the correct data, it must be organized. The way I would organize your data would be the client name in A5 and B5,(last, first) then B6:Z5 or whatever you need would list the Companys. This is some what off a pain, but organizing your information correctly in excel saves a lot of headaches later. The list would look like this:

(last) (first) (company) (company)
a5 b5 c5 d5
Smith Jane Paper Inc. Plain Inc.

a1 would be blank (entry field) a2> =vlookup(a1,list range,column,false)
list range is a5:e26 for example. Needs to be locked. put your curser in this part of the formula and press F4, dollar signs will appear. ($a$5:$e$26) You can select and highlight your range.
column is, a=1 b=2 c=3 etc... This is the line that will appear in your cell
false, dont worry about it, just use it.

Now just fill the cells in a2 to what ever you need, and change the column # by 1 as you go)

If you want, you can email me some sample data and i can configure it for you. [email protected]