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

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

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

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.


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=503364