VLookup

Using this sample data, and the MVLOOKUP function, you can get that. It uses Exact match always.

Here's the UDF code

<pre>'---------------------------------------------------------------------------------------
' Procedure : MVLOOKUP
' DateTime : Ago, 20 2002 16:46
' Author : Juan Pablo González
' Purpose : Return an array of looked values the same way as VLOOKUP.
' but can handle multiple matches. Uses exact matches.
'---------------------------------------------------------------------------------------
'
Function MVLOOKUP(LookedValue As Variant, Matrix As Variant, Column As Integer) As Variant
Dim Result() As Variant
Dim i As Integer
Dim Counter As Long

If IsObject(Matrix) Then Matrix = Matrix.Value

On Error Resume Next
Do
i = i + 1
Counter = UBound(Matrix, i)
Loop Until Err.Number <> 0

If i < Column Then MVLOOKUP = CVErr(xlErrNum): Exit Function
Counter = 0

For i = LBound(Matrix, 1) To UBound(Matrix, 1)
If Matrix(i, 1) = LookedValue Then
Counter = Counter + 1
ReDim Preserve Result(1 To Counter)
Result(Counter) = Matrix(i, Column)
End If
Next i
On Error GoTo 0
If Counter = 0 Then
MVLOOKUP = CVErr(xlErrNA)
Else
MVLOOKUP = Application.Transpose(Result)
End If
End Function</pre>

On 2002-08-20 14:40, SammySpaceman wrote:
Is it possible to have VLookup return more than one value at a time?

Click to expand...

Sure. Here's an example...

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-08-20 14:54

On 2002-08-20 14:53, Mark W. wrote:
Sure. Here's an example...
Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

Click to expand...

Mhmm, now you make me wonder what does the OP mean by "multiple"...

On 2002-08-20 15:21, Juan Pablo G. wrote:

Mhmm, now you make me wonder what does the OP mean by "multiple"...

Click to expand...

Yeah, he wasn't very specific... was he!

he trying to do this:
http://www.mrexcel.com/board/viewtopic.php?topic=19510&forum=2&3

If I understood correctly, then it's Mark's answer.

Hey PaddyD, you just entered a circular reference!!!
This message was edited by Mark W. on 2002-08-20 15:30