Searching an Array

[Crystalyzer]

I am using Excel 2003.

I need to find an item in an array. Basically I have a two dimensional array with 2 "columns" and 10 "rows". "Column" 1 contains ranks as integers and "Column" 2 contains company names. What I want to do is lookup the company name in "column" 2 and return the corresponding rank. Is there any way to do this other than looping through each value in "column" 2 to see if it matches the name I am searching for?

Thanks and best regards,
-Lloyd

 

[Zathras]

If your data were the other way around (company names first, then rank in the 2nd column) you could use VLOOKUP.

If you can't (or don't want to) rearrange the columns you can still use MATCH with INDEX to accomplish your purpose.

 

[Crystalyzer]

Zathras,

Thanks for the quick response. I can rearrange them no problem, however, perhaps I wasn't really clear. I am trying to do this with VBA. Can I still use the "Worksheetfunction.vlookup" on the array variable?

Thanks and best regards,
-Lloyd

 

[Zathras]

Since this is not the VBA forum (see

http://www.tek-tips.com/threadminder.cfm?pid=707)

I gave you the non-VBA answer.

Yes, you can either use WorksheetFunction.Vlookup or perhaps in VBA it would be more appropriate to use the Range.Find method. Your choice.

What "array variable?" perhaps you should post a little bit of the code you are using so that I can understand better.