(Solved) – Return the row number of a match in a 2d array

  • by
(solved)-–-return-the-row-number-of-a-match-in-a-2d-array

I’ve tried to find a solution to this online and for whatever reason simply cannot locate anything (perhaps my search skills are lacking). I have what I feel is a fairly simple query.

I am declaring an array and assigning data to it using the following, which I have confirmed is working:

Dim arr As Variant
arr = Sheet.Range("A1").CurrentRegion

Now, given that this is a 2-dimensional array (only 2 columns of data), I want to then use a match-type of function to return the row number associated with a value in column 1 of the array. My end goal is to then use that to call the corresponding value in column 2 of the array – essentially building an index/match type of scenario but in VBA. However, the Application.Match function fails as this isn’t a 1-dimensional array. See example below:

1|Apple   |Fruit
2|Tomato  |Fruit
3|Carrot  |Vegetable
4|Zucchini|Vegetable

What I am trying to achieve is something like MATCH(“Tomato”, arr(), 0) to get the result of “2” being the row number associate with Tomato. I only need to do my search in column 1 of the array. Does anyone perhaps have any suggestions on the easiest way to do this?

Thanks a mil!

Leave a Reply

Your email address will not be published. Required fields are marked *