Search array for multiple criteria, then write results sequentially to specified cells

Hello,

First time poster long time forum guest browser.

I have encountered a problem I cannot seem to make work with various CSE functions to my satisfaction.

I have an array which I would like to search based on two criteria to return a value from an adjacent cell that meets the criteria to a specific cell in a different sheet within the workbook. The macro then continues to search the array for any other rows matching the criteria and then writes the result to the cell below the previous output cell.

The attached outlines a simplified version of what I am trying to achieve. Where the first colum in the array contains the value(s) that I want returned if the criteria in the adjacent two columns are met, with all lookup values in column meeting the criteria being written sequentially underneath the heading in the adjacent sheet.

The purpose of the macro is to be applied to a sheet with multiple geographic locations (I have used shelfs and aisles as an example) represented visually with the array being dynamic. Placing restrictions on the use of filter options.

Any guidance would eb greatly appreciated. If I'm barking up the wrong tree with thinking a macro is appropriate I'd love to hear any other way of achieving this goal.

Thanks for your help in advance!

I am not sure what you are looking for, but a simple formula in B5

Please Login or Register  to view this content.

Thats almost exactly what I need, however, I need it to return all unit numbers that meet the criteria in the array. I.e your formula returns "S38", but I would like a function that retuns in teh cell immediately below B5 the next value that meets the criteria (in this case "S34"). Which is why i think a macro may be required.

Any ideas as to how to shoehorn teh formula to achieve this?

Code attached.

For your workbook posted.

Please Login or Register  to view this content.

Script out of range means your data are outside the boundary of the array
I was too lazy to use current region which may not work with your actual sheet. Use a range to load the data in to array instead of current region.

Please Login or Register  to view this content.

If you are getting error on these lines it is because the data have not picked up in the current region.

Please Login or Register  to view this content.

Refers to the matching column if your data are in column A-C, so A is one, B is 2 and soon