Need to copy values from a range when matching records located in columns a & b

I have the following scenario where I want to copy values from one range to a matching row based on values in columns A & B, see image below (I show in column D the 1st 7 records from column AE where both material and sample number match.

In row 10 starting at column AB I may have several unique material numbers, these material numbers could be many, I'm only showing 2 in the image.

What we need to do is loop thru each material number in column B and get the adjacent sample number in column A. Then locate the correct column for material number in columns AB onwards then find the matching sample number from column A. Then copy the cell value from the 3rd column to the right (shown as column AE and AI in image) adjacent to the material & sample number in columns A & B.

Capture-5.PNG

Thanks

You could do it with a formula.

Put this in D2 and copy down.

Formula:

Please Login or Register  to view this content.

If you prefer a VBA solution:

Please Login or Register  to view this content.

Thanks for the feedback.

Good idea to learn about the scripting.dictionary. This is a very powerful feature.
To get you started: http://www.snb-vba.eu/VBA_Dictionary_en.html