Assume your datalist on Sheet1 has headers in Row1,
And data in A2 to C50, configured as your example.

On Sheet2, enter the client name to lookup in A2,
*Exactly* as it's entered in Column A of your Sheet1 datalist.

Enter this*array* formula in B2:

=IF(COUNTIF(Sheet1!$A$2:$A$50,$A$2)=ROWS($1:1),IN DEX(Sheet1!B$2:B$50,SMALL(
IF(Sheet1!$A$2:$A$50=$A$2,ROW($1:$49)),ROW(1:1))), "")

Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

*After* the CSE entry, copy the formula to C2.
Then, select both B2 and C2, and copy the *selection* down as far as you
anticipate it's needed.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"bclancy12" wrote in message
...
Sheet 1
Column A Column B Column C
company name -- Agent sales rep Client Address

I was hoping that on sheet 2 I could put a formula in which I could type
out
the client name, and have that generate the sales rep and address. Then,
in
any instance in which there would be more than one match to the company
name,
I was hoping to get a list of the sales reps and addresses so that I can
identify which one is correct. I am starting worry more about validity,
as I
know that Excel will only find the matches if they are exact, and I did
not
create this database, so there may be extra spaces etc, that prevent this
from working correctly.

Can someone help me avoid retyping this whole client list???

"Ragdyer" wrote:

This can be accomplished with array formulas.

Post some simplified examples of your datalist with column and row
references so that suggestions can be made.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"bclancy12" wrote in message
...
unfortunately the client list is about 1500 entries, and we have
clients
like
Washington Mutual and Wells Fargo, and they have so many branches and
agents
that looking for the exact client is still tedious, just trying to
find an
easy way out. I wasn't sure if it was even possible to run some kind
of
lookup that can display multiple results.

"JE McGimpsey" wrote:

If you just want to see which sales reps have claimed a client, do an
autofilter on the client column.

In article ,
bclancy12 wrote:

Does anyone know how to use a vlookup, or any kind of lookup
formula
when
there are more than one result?? ie if I am searching the name of
a
client,
and want to know who the sales rep is, but more than one sales rep
has
labeled the client as "their client", is there a way to see all of
the
names
of the sales rep that have that client on their client list.