software

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 quot;their clientquot;, is there a way to see all of the names
of the sales rep that have that client on their client list.

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

In article gt;,
bclancy12 gt; wrote:

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

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.

quot;JE McGimpseyquot; wrote:

gt; If you just want to see which sales reps have claimed a client, do an
gt; autofilter on the client column.
gt;
gt; In article gt;,
gt; bclancy12 gt; wrote:
gt;
gt; gt; Does anyone know how to use a vlookup, or any kind of lookup formula when
gt; gt; there are more than one result?? ie if I am searching the name of a client,
gt; gt; and want to know who the sales rep is, but more than one sales rep has
gt; gt; labeled the client as quot;their clientquot;, is there a way to see all of the names
gt; gt; of the sales rep that have that client on their client list.
gt;

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 !
---------------------------------------------------------------------------
quot;bclancy12quot; gt; wrote in message
...
gt; unfortunately the client list is about 1500 entries, and we have clients
gt; like
gt; Washington Mutual and Wells Fargo, and they have so many branches and
gt; agents
gt; that looking for the exact client is still tedious, just trying to find an
gt; easy way out. I wasn't sure if it was even possible to run some kind of
gt; lookup that can display multiple results.
gt;
gt; quot;JE McGimpseyquot; wrote:
gt;
gt;gt; If you just want to see which sales reps have claimed a client, do an
gt;gt; autofilter on the client column.
gt;gt;
gt;gt; In article gt;,
gt;gt; bclancy12 gt; wrote:
gt;gt;
gt;gt; gt; Does anyone know how to use a vlookup, or any kind of lookup formula
gt;gt; gt; when
gt;gt; gt; there are more than one result?? ie if I am searching the name of a
gt;gt; gt; client,
gt;gt; gt; and want to know who the sales rep is, but more than one sales rep has
gt;gt; gt; labeled the client as quot;their clientquot;, is there a way to see all of the
gt;gt; gt; names
gt;gt; gt; of the sales rep that have that client on their client list.
gt;gt;The easiest way I can think of would be to create a Pivot Table. Put
Sales Rep field in the Row area and put the Customer field in the Page
area. Then you could filter on the customer and all sales reps
assigned to that customer would be displayed.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???

quot;Ragdyerquot; wrote:

gt; This can be accomplished with array formulas.
gt;
gt; Post some simplified examples of your datalist with column and row
gt; references so that suggestions can be made.
gt; --
gt; Regards,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt; quot;bclancy12quot; gt; wrote in message
gt; ...
gt; gt; unfortunately the client list is about 1500 entries, and we have clients
gt; gt; like
gt; gt; Washington Mutual and Wells Fargo, and they have so many branches and
gt; gt; agents
gt; gt; that looking for the exact client is still tedious, just trying to find an
gt; gt; easy way out. I wasn't sure if it was even possible to run some kind of
gt; gt; lookup that can display multiple results.
gt; gt;
gt; gt; quot;JE McGimpseyquot; wrote:
gt; gt;
gt; gt;gt; If you just want to see which sales reps have claimed a client, do an
gt; gt;gt; autofilter on the client column.
gt; gt;gt;
gt; gt;gt; In article gt;,
gt; gt;gt; bclancy12 gt; wrote:
gt; gt;gt;
gt; gt;gt; gt; Does anyone know how to use a vlookup, or any kind of lookup formula
gt; gt;gt; gt; when
gt; gt;gt; gt; there are more than one result?? ie if I am searching the name of a
gt; gt;gt; gt; client,
gt; gt;gt; gt; and want to know who the sales rep is, but more than one sales rep has
gt; gt;gt; gt; labeled the client as quot;their clientquot;, is there a way to see all of the
gt; gt;gt; gt; names
gt; gt;gt; gt; of the sales rep that have that client on their client list.
gt; gt;gt;
gt;
gt;

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)gt;=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))), quot;quot;)

Array formulas are entered using CSE, lt;Ctrlgt; lt;Shiftgt; lt;Entergt;, instead of the
regular lt;Entergt;, 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!
==============================================
quot;bclancy12quot; gt; wrote in message
news
gt; Sheet 1
gt; Column A Column B Column C
gt; company name -- Agent sales rep Client Address
gt;
gt; I was hoping that on sheet 2 I could put a formula in which I could type
out
gt; the client name, and have that generate the sales rep and address. Then,
in
gt; any instance in which there would be more than one match to the company
name,
gt; I was hoping to get a list of the sales reps and addresses so that I can
gt; identify which one is correct. I am starting worry more about validity,
as I
gt; know that Excel will only find the matches if they are exact, and I did
not
gt; create this database, so there may be extra spaces etc, that prevent this
gt; from working correctly.
gt;
gt; Can someone help me avoid retyping this whole client list???
gt;
gt; quot;Ragdyerquot; wrote:
gt;
gt; gt; This can be accomplished with array formulas.
gt; gt;
gt; gt; Post some simplified examples of your datalist with column and row
gt; gt; references so that suggestions can be made.
gt; gt; --
gt; gt; Regards,
gt; gt;
gt; gt; RD
gt; gt;
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
!
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; quot;bclancy12quot; gt; wrote in message
gt; gt; ...
gt; gt; gt; unfortunately the client list is about 1500 entries, and we have
clients
gt; gt; gt; like
gt; gt; gt; Washington Mutual and Wells Fargo, and they have so many branches and
gt; gt; gt; agents
gt; gt; gt; that looking for the exact client is still tedious, just trying to
find an
gt; gt; gt; easy way out. I wasn't sure if it was even possible to run some kind
of
gt; gt; gt; lookup that can display multiple results.
gt; gt; gt;
gt; gt; gt; quot;JE McGimpseyquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; If you just want to see which sales reps have claimed a client, do an
gt; gt; gt;gt; autofilter on the client column.
gt; gt; gt;gt;
gt; gt; gt;gt; In article gt;,
gt; gt; gt;gt; bclancy12 gt; wrote:
gt; gt; gt;gt;
gt; gt; gt;gt; gt; Does anyone know how to use a vlookup, or any kind of lookup
formula
gt; gt; gt;gt; gt; when
gt; gt; gt;gt; gt; there are more than one result?? ie if I am searching the name of
a
gt; gt; gt;gt; gt; client,
gt; gt; gt;gt; gt; and want to know who the sales rep is, but more than one sales rep
has
gt; gt; gt;gt; gt; labeled the client as quot;their clientquot;, is there a way to see all of
the
gt; gt; gt;gt; gt; names
gt; gt; gt;gt; gt; of the sales rep that have that client on their client list.
gt; gt; gt;gt;
gt; gt;
gt; gt;