Trying to combine INDEX and MATCH functions in VBA

Question

Why doesn't this work?

Result = Application.WorksheetFunction.Index((Range("CountryNames")), _
                Application.Match((Range("B61")), (Range("CurrencyValues")), 0))

Hi, did you enter the formula in a cell and verify that it returns the expected result? — Cool Blue
– Cool Blue, Commented Nov 27, 2013 at 1:21

Jean-François Corbett · Accepted Answer · 2013-11-27 08:55:58Z

1

Too many parentheses. Instead of passing a range to Index, you're only passing the range's value. That is OK for the arguments to Match, since that function works just as well on values as it does for ranges. But Index can only work with ranges. Try this instead:

Result = Application.Index(Range("CountryNames"), _
        Application.Match(Range("B61"), Range("CurrencyValues"), 0))

edited Nov 27, 2013 at 8:55

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

answered Nov 27, 2013 at 1:26

Chel

2,6231 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to combine INDEX and MATCH functions in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related