formula to pull information based on text

Hi

I have had a long break from working with Excel/formulas so hoping someone could be of assistance. I have included a test sheet as an example.

I would like to be able to pull information from the "pasted data" tab only if the "area" from B1 on the check tab is found in column H on the pasted data tab. If it is, then to pull the information required from pasted data(A, B, G, H) shown on the check tab. Ideally I would like to pull only ageing information too so only show if more than 0 days old.

Hope this makes sense.

Regards

Put this formula in cell J2 of the Pasted Data sheet:

=IF(AND(H2=check!$B$1,'pasted data'!B2<0),MAX(J$1:J1)+1,"-")

and copy this down as far as you like - the hyphens will help to show which rows the formula is active on.

Then in A4 of the Check sheet you can use this formula:

=IFERROR(INDEX('pasted data'!A:A,MATCH(ROWS($1:1),'pasted data'!$J:$J,0)),"")

Copy this across to cells B4:C4. In cell C4 you need to amend the formula so that the C:C (equivalent to the A:A shown in red above) is changed to G:G, as that is where you want to get the data from, and then you can copy the formula from C4 into D4. Then you can copy the four formulae from A4:D4 down to fill your table, and then use the drop-down in B1 to change the area and see the displayed data change accordingly.

Hope this helps.

Pete