Replicating a 2 variable index and match

Morning all,

I'm currently running into a road block with replicating a formula which matches a value only when 2 variables are matched.

I had some limited success with using 2 declared variables and using while x = x and y = y however it wasn't dynamic enough to get all the paired variables.

I've attached a spreadsheet with sample data, basically what i'm trying to achieve is....

I have a very large data set which is loosely set up like columns F, G & H; and another data set, which is out of my control in shape and format, as shown in columns B, C & D. All i want to do is return the correct cost per pairing of JobCode & Workstage.

I can appreciate this is probably the simplest of problems but any help would be greatly appreciated.

Many thanks,

Harry.

Because this will form part of a larger routine that i'm running on a very large data set.

The formula I had in before is taking too long to run so optimising via VBA was the preferred solution.

Thanks,

Harry.

VBA

Please Login or Register  to view this content.

Originally Posted by jindon

VBA

Please Login or Register  to view this content.

That seems to work absolutely perfectly, however i can't understand how.

Sorry to be a pain - but could you elaborate?

Many many thanks.

Harry.

Loop through look up table, F4:Hx.

Store Col.H value in item property of dictionary for each unique key value of concatenate string Col.F & Col.G.

Loop through the data table and output the stored item value in dictionary for key concatenate string of Col.B & C.

Get vb help for dictionary object in detail...

Also used Array for speeding up.

Thankyou very much.