Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/VLookup
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/VLookup
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/VLookup
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/VLookup
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/VLookup
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/VLookup
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/VLookup
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
student needing help w/vlookup function grading criteria | Excel Worksheet Functions |