Matching two values in same column and returning value from one row

Hi Guys,

In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).

In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.

The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.

Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.

So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.

Thanks
Rooboyz

Hi,
I really do not know the formula that can be usefull, but look at private function that works as needed:

Please Login or Register  to view this content.

Sample8.xlsm

Hope it helps

Regards

M