Formula to lookup a wildcard value and return a value if true

From a profit and loss report, I want to extract all data in the row relative to one department into another sheet - otherwise I want a blank return, in this case the department that has the suffix code .2202. What is the best way to do this please - I have tried vlookup but only know basics

30200.2202 P - ACC Levy 886.3
30400.9202 A - Accounting Fees 12,300.00
30600.2202 P - Advertising 1,394.73
30600.3202 W - Advertising 206.4
30600.4122 S - Advertising 1,134.47
30600.9202 A - Advertising 186.07

Try

=IFERROR(INDEX(A$1:A$6,SMALL(IF(RIGHT($A$1:$A$6,4)="2202",ROW($A$1:$A$6)-ROW($A$1)+1,""),ROWS($A$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy acroos and down

OR,

Try the following array formula:

Please Login or Register  to view this content.

to be confirmed by pressing CTRL+ALT+ENTER

See the attached file.

I have attached (hopefully) a generic working document that shows what I am trying to do more clearly - On sheet "412" I want to pull through all entries that have ".412" in column A - and pull through all the information in that row (So columns A through to F).

Attachment 546968