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
<mailto:excel-macros%2bunsubscr...@googlegroups.com> .
--
Thanks & regards.
Ashish Bhalara
9624111822
PPlease 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.
