VLookup Question

[Nikki91775]

Hi, I have a question regarding VLookup. I have a company called Odessa, however, they have three different accounts with three different names. I am trying to create a one line lookup of all three accounts. Is there a way to do a V lookup with some paramaters like _Vlookup, "ODE" ......? Let me know if you know of a workaround.


Thanks.

 

[Debraj]

Hi Nikki..


Try

=Vlookup("ODE" & *,rangetoLookup,No,True)

It will search all Company starting with ODE...


Regards,

Deb

 

[Nikki91775]

Hi Deb, thank you for your help. In your formula, where would I put my range to lookup? This is what I tried and it gave me an error.


=VLOOKUP(("ODE" & *, Lookup!A278:B316, 2, No, True)


I know I am doing something wrong. Just cannot figure it out. Thanks.

 

[jason]

Nikki,


it would depend on your data layout... what column contains the Company names? If i have names starting in A2 down to A100 and my next col is each companies account number, contact name, etc. my range would be A2:D100. the key with the vlookup is that the first part of your lookup range MUST be where the lookup value should be. meaning, if im looking for someones name, i specify A1:C10 as my range but Col A contains phone numbers, it will never find the name you are looking for (obviously!)


i see an issue with the formula you posted... 1) too many "("'s ..could be a copy/paste error but either way it shouldnt cause the formula to error. 2) "NO" should not be in this function. there are only 4 arguments/parts; the LookupValue ("ODE"&*),LookUpRange (a278:b316), ColumnNo (this represents which column you want to get the data from), and True/False. (True= approx it match & False=Exact match). Since your LookupRange consists of Col A and Col B, you would put 1 for your ColumnNo if you want the content in ColA returnee or use 2 for col. B.


if your other arguments are accurate it should be:

=VLOOKUP("ODE"&*,Lookup!A278:B316,2,True)


if you need me better explain anthing i just rambled on about, let me know!

 

[Nikki91775]

Hi,


The formula still is not working. In my pivot table - column A is the vendor name. And Odessa is listed three times. In column B is the amount of current orders. So in my main sheet - I have Odessa Listed and CYTD #'s. Usually with a Vlookup - I would click on the cell in the main sheet - =Vlookup(C1, Lookup!A278:B316, 2, False) and it will give me the value but only for the Odessa that has the one name, not all three. I am not sure why it is not working.

 

[NARAYANK991]

Hi Nikki ,


When using a wildcard , you need to have it within quotes "*" ; so , your earlier example would be :


=VLOOKUP("ODE" & "*", Lookup!A278:B316, 2, True)


The parentheses were wrong , and the number of parameters was also wrong.


Whether the last parameter should be TRUE or FALSE depends on whether your column A is sorted or not.


Lastly , the VLOOKUP will only return the first match ; if you want to sum all the amounts relating to the company whose name starts with Odessa , you cannot use the VLOOKUP function ; you need to use either SUMIF or a SUM(IF(...)) array formula.


Narayan

 

[jason]

It may be the lookup value... i havent tried that before.... or it could be due to it being a pivottable...


i'll keep playing


question:


so lets say in your data, you have 4 lines that apply to Odessa... the vlookup is meant to find 1 record of data and return a specified value. can you explain how you plan to use this? depending on the type of results you're after, there may be a better suited approach

 

[Nikki91775]

The pivot I am pulling is from a database and I need to understand each of my distributor's, orders for this year vs. prior year and it also ranks the distrubitors based on orders - 1, 2, etc. So in my main view I have all my distributors listed in a pretty form with their numbers. I hit a data refresh each month to update from the prior month and it automatically puts numbers in and arranges. We use this for overall and then I break it out by region so my sales guys can see who their top distributors are.


My pivot table is pretty simple - only 4 columns, but this one distributor and it is my one line is listed 4 different times because somehow accounting has chosen to put them in that way. I wish I could go to accounting and have them change it, but that is near impossible so I need a workaround. Right now when I use this:


=vlookup("*ODE*", Lookup!A1:B316, 2, False), it comes up with just one of the Odessa's. I know there has to be a way to pull them all rather than just one value.