Parameterized use of the match function

prw8864

New Member
Joined
Nov 5, 2011
Messages
3
I am trying to make the following function call w/ MATCH

found = WorksheetFunction.Match(num_months, lookupRC(1), 0)
where lookupRC(1) is a string holding the named range that I need to use.

How can I represent this named range in such a way the MATCH function will accept it as the array_value discused in the MS help dialog.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Perhaps

Code:
found = WorksheetFunction.Match(num_months, Range(lookupRC(1)), 0)
 
Upvote 0
I have tried to use the lookupRC(1) variable in the RANGE function but I keep getting an error saying the match function can't read the array_value parameted. I have also tried to convert the named range into RC cordinates as a quoted string with no luck.... I have also tried "ThisWorkbook.Names(lookupS(0)).RefersToRange" to make a rang but have not been able to get this to work either..
 
Upvote 0
Are you sure you don't have it backwards? This works fine for me:

Code:
MsgBox WorksheetFunction.Match( _
    Range("foobar")(1, 1).Value, Range("A1:A10"), 0)
 
Upvote 0
Is lookupRC(1) just the name of the range you want to use in the lookup?
 
Upvote 0
LookupRC(1) holds the text representation of the named range to be used. I have several table that may be used. I have a function that returns 1) the named range of the table 2) the named range of the column to be searched. These items are stored in a 2 element array LookupRC.

If the tabel to be used is "month_6" then LookupRC(1) has the value month_6 (with out surounding quotes).
 
Upvote 0
The code VoG posted worked fine for me with a one dimensional array LookupRC where LookupRC(1) = "month_6", where month_6 was a named range with one column.

What are the dimensions of the array?

What are the dimensions of the range(s)?

A range used in MATCH can only be one column wide

Also what's the error message?

I've never heard of of an error message like the one you mention.

If there's a problem with the named range the error is Unable to get the Match property... unless you use Application.Match.
 
Upvote 0

Forum statistics

Threads
1,223,491
Messages
6,172,591
Members
452,468
Latest member
godlennutrition

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
Back
Top