# VLookup

#### SammySpaceman

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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
No - what are you trying to do?

Using this sample data, and the MVLOOKUP function, you can get that. It uses Exact match always.
Libro4
ABCDE
1a1a1
2a22
3b34
4a4
Hoja1

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?

Sure. Here's an example...
Book1
ABCDEFG
1ApplesA10A10
2BananasB20
3GrapesC30
4OrangesD40
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

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

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!

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

Replies
3
Views
436
Replies
5
Views
354
Replies
8
Views
238
Replies
5
Views
273
Replies
1
Views
177

1,203,181
Messages
6,053,966
Members
444,695
Latest member
asiaciara

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back