Application.WorksheetFunction.Lookup(using array variable)

MattOCUSA

Board Regular
Joined
Jan 12, 2007
Messages
231
If I am doing an Application.WorksheetFunction.Lookup, can I reference a variable I have that is a multi-dimensional Array(2 Rows, 10 Columns) in the lookup?

So I want to lookup:
1) myValue = (which lets say equals the number in Cell ("A1").

2) I want to lookup myValue in the first column of my 2 column Array, called myArray, and give me the results found in the 2nd column of myArray? That must be possible? Could I get a syntax example?

I use 3 variables now: myValue, vFind and vReturn. I want to replace vFind and vReturn with myArray?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
Sub Sample()
Dim myArray, myValue, myResult
' generate 2d array
myArray = [{1,"Number1";2,"Number2";3,"Number3";4,"Number4"}]
myValue = Range("a1").Value
On Error Resume Next
myResult = WorksheetFunction.VLookUp(myValue, myArray, 2, False)
If Err <> 0 Then myResult = "#N/A"
MsgBox myResult
End Sub
 

MattOCUSA

Board Regular
Joined
Jan 12, 2007
Messages
231
Cool, works. It was missing a "{" initially but other than that, worked like a charm!

Your the man. :LOL:

I notice you caught the "{" since I initially copied it, thank you again!!!
 

Forum statistics

Threads
1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top