Consolidate a list from range with 2 criteria

[ABSTRAKTUS]

Yo all!

What I have here is a simple stock consolidation spreadsheet with 3 sheets in - "DATA", "SAP" & "STOCKS". "DATA" sheet is a basic product parameters database, "SAP" is stock report download from business software, "STOCKS" is where I want only specific stock figures going on.

There are 2 criteria that will filter the end result:

• All products with distributor "SHOP" on a "DATA" sheet
• All products with status "LIVE" as per "DATA" sheet

The aim is to match those 2 criteria and return all matching figures from sheet "SAP" onto sheet "STOCKS".

How do I go about it? A dummy is attached...

Thanks in advance for your priceless help!

[sintek]

Here's one way:

Please Login or Register  to view this content.

[ABSTRAKTUS]

Hi sintek and thanks for a response.

Your code works, but it only picks up the first matched value from SAP sheet. I need to return all entries, that match criteria. Any thoughts?

[jindon]

Please Login or Register  to view this content.

[sintek]

Your code works, but it only picks up the first matched value from SAP sheet. I need to return all entries, that match criteria. Any thoughts?

Code tested on your sample works... Picks up 3 criteria 101 105 109

[ABSTRAKTUS]

Hi Jindon and thanks for your response. Code works just perfect! Thanks!

[ABSTRAKTUS]

For sintek - appreciate it does pick up the criteria, but returns only one entry per matched criteria, when there are more. Anyway, thanks to you too.

[AlanY]

my humble attempt with formula