Using MATCH and INDEX in VBA (Excel)

[ninja1980]

Hi

I regularly use the Excel Functions INDEX() and MATCH() to return values in look up tables from input data. For example:

=INDEX(Array,MATCH(43,Array2,2)+1,2)

I really want to be able to perform this function in VBA in the most efficient way possible. Can anyone give me any tips?

Thanks

 

[xlbo]

really depends on what you are trying to achive and the setup of your data - also depends on whether you want to return the result to code or to the worksheet

myValue = application.INDEX(Sheets("Sheetname").Range("Range Definition"),application.MATCH(43,Sheets("Sheetname").Range("Range Definition"),2)+1,2)


or

myValue = Sheets("Sheetname").range("Range Definition").Find(43).offset(0,offset columns)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question