Need to pull matching values from another workbook using index match

Hello fellow excel junkies -Here is a bit of challenge for your brains.

I want to extract the status (received /unreceived) of an item in the "revision" column D of the "Bill Of materials report" spreadsheet (attached) from the "delivery status" column E of the "purchasing report" (attached) into a separate column in the "Bill Of materials report" spreadsheet.

The corresponding item number column in the purchasing report is "Supplier SO" Column F in the "purchasing report".

It is possible that the same item can be ordered under different jobs so it must retrieve status for only those items that were ordered under the job number "80145" in cell B3 of the "Bill Of Materials Report" spreadsheet. The corresponding jobs column on the "Purchasing Report" is column A "Test Job#".

I want to accomplish this using excel VBA as I dont want to put in the retrieved values instead of formulas. It will make the file size too big.

I was able to accomplish this partially (without the job number criteria) using the index match formula below but not sure how I can write it in VB code. What makes it tricky is that there is a space in the item number entered in the "purchasing report" so I had to use the trim function to get rid of this space so it must be entered as an array formula. So I will need to use activesheet.evaluate instead of worksheetfunction and I cannot figure that part out. Any help will be greatly appreciated!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks Nigel. Sorry I didn't know earlier but now there is another twist. There can be multiple entries belonging to the same Item number in the purchasing report for the same job and we need to collect the status for all matches returned and if any of them are still unreceived, then it should show "unreceived" otherwise it shows "received". unless someone here has a better idea, I will have to go with the array solution as it is getting more complex