Thanks Rajan, its working fine, thank you very much.
Regards
Ashsish Bhalara
On Thu, Nov 8, 2012 at 7:12 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote:
> *I have just Change*
>
> * *
>
> *LookupRange.Address to LookupRange.Address(, , , 1)***
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> * *
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Kanwaljit Singh
> *Sent:* 08 November 2012 7:11
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ K.A. Mr.Rajan Verma: Help need to get
> multiple values by vlookup****
>
> ** **
>
> Dear Rajan,****
>
> ** **
>
> Kindly tell Exactly what changes or modifications you have made to the
> code !****
>
> On Thu, Nov 8, 2012 at 6:31 PM, Rajan_Verma <rajanverma1...@gmail.com>
> wrote:****
>
> *You can use this one *****
>
> *Function MLOOKUP(TableArray As Range, ByVal LookupVal, LookupRange As
> Range, _*****
>
> * Optional ByVal
> NthMatch As Long)*****
>
> * *****
>
> *' Author : Krishnakumar @ ExcelFox.com*****
>
> *‘Modified by :- Rajan Verma @ ExcelPoweruser.wordpress.com*****
>
> * *****
>
> * *****
>
> *If Not TypeOf TableArray Is Range Then*****
>
> * MLOOKUP = CVErr(2042)*****
>
> * Exit Function*****
>
> *End If*****
>
> *If Not TypeOf LookupRange Is Range Then*****
>
> * MLOOKUP = CVErr(2042)*****
>
> * Exit Function*****
>
> *End If*****
>
> *If TableArray.Rows.Count <> LookupRange.Rows.Count Then*****
>
> * MLOOKUP = CVErr(2042)*****
>
> * Exit Function*****
>
> *End If*****
>
> *If TableArray.Columns.Count <> LookupRange.Columns.Count Then*****
>
> * MLOOKUP = CVErr(2042)*****
>
> * Exit Function*****
>
> *End If*****
>
> * *****
>
> *Dim LV_Cnt As Long 'Count Loookup Value*****
>
> *Dim KA1, KA2*****
>
> *Dim r As Long, c As Long*****
>
> *Dim fFoundNo As Long*****
>
> *Dim n As Long*****
>
> *Dim strLval As String*****
>
> * *****
>
> *If IsNumeric(LookupVal) Then*****
>
> * LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," &
> LookupVal & ")")*****
>
> * fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," &
> LookupRange.Address(, , , 1) & ",0)")*****
>
> *ElseIf IsDate(LookupVal) Then*****
>
> * LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," &
> CLng(LookupVal) & ")")*****
>
> * fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," &
> LookupRange.Address(, , , 1) & ",0)")*****
>
> *Else*****
>
> * strLval = """" & LookupVal & """"*****
>
> * LV_Cnt = Evaluate("countif(" & LookupRange.Address(, , , 1) & "," &
> strLval & ")")*****
>
> * fFoundNo = Evaluate("match(" & strLval & "," & LookupRange.Address(,
> , , 1) & ",0)")*****
>
> *End If*****
>
> * *****
>
> *If NthMatch > 0 Then*****
>
> * If LV_Cnt = 0 Or NthMatch > LV_Cnt Then*****
>
> * MLOOKUP = CVErr(2042)*****
>
> * Exit Function*****
>
> * End If*****
>
> *End If*****
>
> * *****
>
> * *****
>
> *KA1 = TableArray: KA2 = LookupRange*****
>
> * *****
>
> *For r = fFoundNo To UBound(KA1, 1)*****
>
> * For c = 1 To UBound(KA1, 2)*****
>
> * If LCase$(KA2(r, c)) = LCase$(LookupVal) Then*****
>
> * If NthMatch Then*****
>
> * n = n + 1*****
>
> * If n = NthMatch Then*****
>
> * MLOOKUP = KA1(r, c)*****
>
> * Exit Function*****
>
> * End If*****
>
> * Else*****
>
> * MLOOKUP = MLOOKUP & "," & KA1(r, c)*****
>
> * End If*****
>
> * End If*****
>
> * Next*****
>
> *Next*****
>
> *MLOOKUP = Mid$(MLOOKUP, 2)*****
>
> *End Function*****
>
> * *****
>
> * *****
>
> *Regards*****
>
> *Rajan verma*****
>
> *+91 7838100659 [IM-Gtalk]*****
>
> * *****
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Ashish Bhalara
> *Sent:* 08 November 2012 4:25
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ K.A. Mr.Rajan Verma: Help need to get
> multiple values by vlookup****
>
> ****
>
> Hi Mr.Rajan****
>
> Thanks to sharing useful link of Mlookup but there is a problem in sheet
> that the function working only same worksheet, if i link to another sheet
> the function is not working. Kindly reply for this solution.****
>
> ****
>
> Thanks & regards****
>
> Ashish Bhalara****
>
> On Thu, Oct 25, 2012 at 8:53 AM, Rajan_Verma <rajanverma1...@gmail.com>
> wrote:****
>
> Hi ****
>
> ****
>
> Download the file from here****
>
> ****
>
> http://www.excelfox.com/forum/f12/vlookup-return-multiple-values-4/****
>
> ****
>
> Thanks****
>
> Rajan.
>
> On Wednesday, 24 October 2012 22:51:01 UTC+5:30, saravanan R wrote:****
>
> Hi xlts,
>
> Please help me, on how to get the all the associated items In the range
> where it as more than 1 lookupvalue.
>
> Thanks,
> Saravanan****
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
> ****
>
>
>
> ****
>
> ****
>
> --
> Thanks & regards.****
>
> ****
>
> Ashish Bhalara****
>
> 9624111822****
>
> P*Please do not print this email unless it is absolutely necessary.
> Spread environmental **ü**awareness.**♣♣♣*****
>
> ****
>
> ****
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
> ****
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
> ****
>
>
>
> ****
>
> ** **
>
> --
> Sincere Regards
> CA Kanwaljit Singh Dhunna
> +91-987 6739 911
> +91-950 1031 007****
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
> ****
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
>
>
--
Thanks & regards.
Ashish Bhalara
9624111822
P*Please do not print this email unless it is absolutely necessary. Spread
environmental üawareness.♣♣♣*
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
