I think its not possible by vlookup() Function ..
On Sat, Aug 20, 2011 at 9:38 PM, Sam Mathai Chacko <samde...@gmail.com>wrote:
> Not sure why you want to use only Vlookup. VL only looks up the exact, or
> the approximate match from a list. If you are worried about the complexity,
> just use a simple combination of sumproduct and large.
>
> =SUMPRODUCT(LARGE(($A$2:$A$16=H7)*(B2:B16),3))
>
> The advantage here is that you do not even need to use an error handling.
> If your number does not exist in the list, it will return all zeroes as 0 0
> 0
>
> Cheers,
> Sam Mathai Chacko (GL)
>
>
> On Sat, Aug 20, 2011 at 5:19 PM, Indrajit $nai <talk2indra...@gmail.com>wrote:
>
>> *Thanks to everyone. But one thing, is it possible to get the correct
>> result with the help of "Vlookup" formula?*
>>
>>
>>
>> On Fri, Aug 19, 2011 at 8:11 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote:
>>
>>> Thanks and Welcome
>>>
>>>
>>> -----Original Message-----
>>> From: excel-macros@googlegroups.com [mailto:
>>> excel-macros@googlegroups.com]
>>> On Behalf Of Lawali
>>> Sent: Friday, August 19, 2011 7:57 PM
>>> To: MS EXCEL AND VBA MACROS
>>> Subject: Re: $$Excel-Macros$$ Need your help in Vlookup formula.....
>>>
>>> Rajan,
>>> Your solution is awesome!!!
>>>
>>> On Aug 19, 9:39 am, "Rajan_Verma" <rajanverma1...@gmail.com> wrote:
>>> > You Can Tri this :
>>> >
>>> > Private Sub Worksheet_Change(ByVal Target As Range)
>>> >
>>> > On Error GoTo Err:
>>> >
>>> > Dim k As Integer
>>> >
>>> > If Target = Range("H7") Then
>>> >
>>> > Application.EnableEvents = False
>>> >
>>> > Range("F9").Resize(1, Range("F9").End(xlToRight).Row).ClearContents
>>> >
>>> > k = 0
>>> >
>>> > For i = 1 To Range("A65536").End(xlUp).Row
>>> >
>>> > If Range("A" & i).Value = Target Then
>>> >
>>> > Range("F9").Offset(0, k).Value = Range("A" & i).Offset(0,
>>> > 1).Value
>>> >
>>> > k = k + 1
>>> >
>>> > End If
>>> >
>>> > Next
>>> >
>>> > End If
>>> >
>>> > Err:
>>> >
>>> > Application.EnableEvents = True
>>> >
>>> > End Sub
>>> >
>>> > From: excel-macros@googlegroups.com [mailto:
>>> excel-macros@googlegroups.com]
>>> > On Behalf Of ashish koul
>>> > Sent: Friday, August 19, 2011 9:20 AM
>>> > To: excel-macros@googlegroups.com
>>> > Subject: Re: $$Excel-Macros$$ Need your help in Vlookup formula.....
>>> >
>>> > try this
>>> >
>>> > On Fri, Aug 19, 2011 at 1:02 AM, Indrajit $nai <
>>> talk2indra...@gmail.com>
>>> > wrote:
>>> >
>>> > Hi All,
>>> >
>>> > I need your small help in Vlookup formula. I have attached a file with
>>> this
>>> > mail, where you can see 2 columns in the left. One is Qty. another is
>>> Sr.
>>> > no.
>>> >
>>> > Now I want to search Sr. no. through Qty. Suppose if I put Qty. 1400 it
>>> will
>>> > show me 14, if I put 600 it will show 6, but the problem is when I am
>>> > putting
>>> >
>>> > 100 or 200 in the input column it is only giving me the Sr. No. 1 (in
>>> case
>>> > of 100) or 2 (in case of 200). But 100 or 200 are available more than
>>> one
>>> > time, so
>>> >
>>> > it should give me the result like this;
>>> >
>>> > Please enter the Sr. No. ---> 100
>>> >
>>> > Qty 1 7 9
>>> >
>>> > How can I do this with the help of Vlookup. Can you help me. Please its
>>> > urgent.
>>> >
>>> > Thanks in advance.
>>> >
>>> > --
>>> > Indrajit Snai
>>> >
>>> > Disclaimer:
>>> > This electronic message and any files transmitted with it are
>>> confidential
>>> > and intended solely for the use of the individual or entity to whom
>>> they
>>> are
>>> > addressed. If you are not the intended recipient you are hereby
>>> notified
>>> > that any disclosure, copying, distribution or taking any action in
>>> reliance
>>> > on the contents of this information is strictly prohibited and may be
>>> > unlawful.
>>> >
>>> > --
>>> >
>>>
>>> ---------------------------------------------------------------------------
>>> -
>>> > ------
>>> > Some important links for excel users:
>>> > 1. Follow us on TWITTER for tips tricks and links
>>> :http://twitter.com/exceldailytip
>>> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
>>> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
>>> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
>>> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>>> >
>>> > To post to this group, send email to excel-macros@googlegroups.com
>>> >
>>> > <><><><><><><><><><><><><><><><><><><><><><>
>>> > Like our page on facebook , Just follow below
>>> linkhttp://www.facebook.com/discussexcel
>>> >
>>> > --
>>> >
>>> > Regards
>>> >
>>> > Ashish Koul
>>> >
>>> > <http://akoul.blogspot.com/>
>>> akoul.blogspot.comhttp://akoul.posterous.com/
>>> >
>>> > <http://akoul.wordpress.com/> akoul.wordpress.com
>>> >
>>> > My <http://in.linkedin.com/pub/ashish-koul/10/400/830> Linkedin
>>> Profile
>>> >
>>> > P Before printing, think about the environment.
>>> >
>>> > --
>>> >
>>>
>>> ---------------------------------------------------------------------------
>>> -
>>> > ------
>>> > Some important links for excel users:
>>> > 1. Follow us on TWITTER for tips tricks and links
>>> :http://twitter.com/exceldailytip
>>> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
>>> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
>>> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
>>> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>>> >
>>> > To post to this group, send email to excel-macros@googlegroups.com
>>> >
>>> > <><><><><><><><><><><><><><><><><><><><><><>
>>> > Like our page on facebook , Just follow below
>>> linkhttp://www.facebook.com/discussexcel
>>> >
>>> > Copy of Vlookup (2).xls
>>> > 54KViewDownload
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------
>>> ------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>> Hi All,
>>
>> I need your small help in Vlookup formula. I have attached a file with
>> this mail, where you can see 2 columns in the left. One is Qty. another is
>> Sr. no.
>> Now I want to search Sr. no. through Qty. Suppose if I put Qty. 1400 it
>> will show me 14, if I put 600 it will show 6, but the problem is when I am
>> putting
>> 100 or 200 in the input column it is only giving me the Sr. No. 1 (in case
>> of 100) or 2 (in case of 200). But 100 or 200 are available more than one
>> time, so
>> it should give me the result like this;
>>
>> *Please enter the Sr. No. *---> 100
>> *Qty * 1 7 9
>>
>> How can I do this with the help of Vlookup. Can you help me. Please its
>> urgent.
>>
>> Thanks in advance.
>>
>> --
>> Indrajit Snai
>>
>> Disclaimer:
>> This electronic message and any files transmitted with it are confidential
>> and intended solely for the use of the individual or entity to whom they are
>> addressed. If you are not the intended recipient you are hereby notified
>> that any disclosure, copying, distribution or taking any action in reliance
>> on the contents of this information is strictly prohibited and may be
>> unlawful.
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
>
> ----------------------------------------------------------------------------------
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Regards
Rajan verma
+91 9158998701
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
