nested iferror index match for 10k rows

[bruno08102013]

hello, i am trying to insert a nested iferror/index/match formula that basically searches for a value, if its missing checks for the value in the next list, and so on

the problem is there are about 10,000 rows and even with the below code the macro is painfully slow (longer than 20 mins). Additionally i would like to use slight variations of this formula in a few more columns, which makes it even more daunting.

Please note the number of rows will be dynamic so the formula must be inserted in every row until row is blank (which it currently does as written)

Can someone kindly review the below code and let me know I could accomplish this faster?

Thanks!!!!

Please Login or Register  to view this content.

[rcm]

try using the Range.find()

[TMS]

See the attached link and sample workbook:

http://www.excelforum.com/excel-form...-in-excel.html

The original thread returns values, using an index/match formula, across several rows and columns.

The formula provided is only interested in if the lookup value exists anywhere and, if it does, what row is it on, so it can index another column.

I have added two formulae, based on the original formula to return just the row and the column.

B2: Value

Please Login or Register  to view this content.

Q2: Row

Please Login or Register  to view this content.

R2: Column

Please Login or Register  to view this content.

See the attached workbook.

At this point, it has nothing to do with VBA. But it might be possible to adapt these formulae to meet your needs rather than the nested IF statements that you currently have.

If you post a sample workbook, we might be able to make that adaptation.

Regards, TMS

[bruno08102013]

thank all of you for the suggestions, how would I implement Range.find() in my existing code?