vlookup with two or more matches

[Trevor Osborne]

=IFERROR(IFERROR( VLOOKUP(A:A,Sheet1!A:C,3,FALSE),VLOOKUP(A:A,Sheet1!B:C,2,FALSE)),"") this the vlookup that I am using. How can I have this show if there is more then one match on sheet1?

<colgroup><col width="746" style="width: 560pt; mso-width-source: userset; mso-width-alt: 26042;"> <tbody> </tbody>

 

[alansidman]

Here is some code to create a UDF.

Function MLOOKUP(ByRef TableArray As Range, ByVal LookupVal, ByRef LookupRange As Range, _
                                                        Optional ByVal NthMatch As Long)


' Author        : Krishnakumar @ ExcelFox.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 & "," & LookupVal & ")")
    fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address & ",0)")
ElseIf IsDate(LookupVal) Then
    LV_Cnt = Evaluate("countif(" & LookupRange.Address & "," & CLng(LookupVal) & ")")
    fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address & ",0)")
Else
    strLval = """" & LookupVal & """"
    LV_Cnt = Evaluate("countif(" & LookupRange.Address & "," & strLval & ")")
    fFoundNo = Evaluate("match(" & strLval & "," & LookupRange.Address & ",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

Here is an explanation of how it works.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	4/1/2011	SN	181	*	*	*	*	*	*	*	*	*	*	*	*
2	4/2/2011	WP	825	*	vm	509	509,592,717,359,172,287,556,211	*	*	*	*	*	*	*	*
3	4/3/2011	QO	512	*	QO	592	512,471,906,981	*	*	*	*	*	*	*	*
4	4/4/2011	JK	354	*	DC	717	518,259,826,820	*	*	*	*	*	*	*	*
5	4/4/2011	SV	394	*	*	359	*	*	*	*	*	*	*	*	*
6	4/4/2011	OF	921	*	*	172	*	Syntax	*	*	*	*	*	*	*
7	4/4/2011	DC	518	*	*	287	*	MLOOKUP(TableArray,LookupValue,LookupRange,[NthMatch])	*	*	*	*	*	*	*
8	4/4/2011	GD	348	*	*	556	*	*	*	*	*	*	*	*	*
9	4/9/2011	VM	509	*	*	211	*	If the optional NthMatch is missing, All the values will be concatenated in a single cell	*	*	*	*	*	*	*
10	4/10/2011	MI	193	*	*	#N/A	*	*	*	*	*	*	*	*	*
11	4/11/2011	BA	329	*	*	#N/A	*	*	*	*	*	*	*	*	*
12	4/12/2011	AI	930	*	*	#N/A	*	Click on Alt +F11 for the VBA code to make this UDF. *Copy it and paste it	*	*	*	*	*	*	*
13	4/13/2011	XJ	777	*	*	*	*	into your worksheet	*	*	*	*	*	*	*
14	4/14/2011	OH	962	*	*	*	*	*	*	*	*	*	*	*	*
15	4/15/2011	FS	221	*	*	*	*	*	*	*	*	*	*	*	*
16	4/16/2011	RU	419	*	4/4/2011	394	*	*	*	*	*	*	*	*	*
17	4/17/2011	KU	115	*	*	*	*	*	*	*	*	*	*	*	*
18	4/18/2011	LG	599	*	*	*	*	*	*	*	*	*	*	*	*
19	4/19/2011	WJ	657	*	*	*	*	*	*	*	*	*	*	*	*
20	4/20/2011	AM	105	*	*	*	*	*	*	*	*	*	*	*	*
Sheet1

 

[Trevor Osborne]

Thank you for your quick response, i will give it a go.