VLookup

[SammySpaceman]

Is it possible to have VLookup return more than one value at a time?

 

[PaddyD]

No - what are you trying to do?

Paddy

 

[Juan Pablo González]

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

Libro4
	A	B	C	D	E
1	a	1		a	1
2	a	2			2
3	b	3			4
4	a	4
Hoja1

 

[Juan Pablo González]

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>

 

[Mark W.]

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

Sure. Here's an example...

Book1
	A	B	C	D	E	F	G
1	Apples	A	10		A	10
2	Bananas	B	20
3	Grapes	C	30
4	Oranges	D	40
5
Sheet1

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

 

[Juan Pablo González]

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".

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

 

[Mark W.]

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

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

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

 

[PaddyD]

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

 

[Juan Pablo González]

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

 

[Mark W.]

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