Formula to look up various rows of a particular code

[cuty]

I need a formula to solve my problem below:

I have a spreadsheet (Sheet A) with a report for which column A has different nominal codes.
In a separate sheets, for example Sheet B i need to create a formula whereby it looks up a particular nominal code (Eg. 6270) from sheet A and all the row adjacent to it. There would be multiple rows.
In another sheet C, same formula which looks up another nominal code (e.g 6280). ETc Etc

An attachment is also upload to view my problem

Thank you so much

[Glenn Kennedy]

use this array formula in A6, copied across and down:

=IFERROR(INDEX('Sheet A (downloaded report)'!B:B,SMALL(IF(--LEFT('Sheet A (downloaded report)'!$B$2:$B$7,4)=6270,ROW('Sheet A (downloaded report)'!$B$2:$B$7)),ROWS(A$6:A6))),"")

You'll need to change the references iin the INDEX bit to account for the hidden columns (see my formula in your sheet)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[cuty]

Woow thank you so much for your fast reply.

I am trying to drag down the formula below (for more rows). Is this where you say you need to press cntrl-shift-enter ? Since nothing is happening and is getting blank cells.

[cuty]

File updated and complicated it a bit further, since the downloaded report would be constantly updated. However, something is not quite good. Both sheets are only getting part of the info. And I am finding difficult to drag the formula to the rows below.

thanks for your help

[sanram]

After typing a formula generally we press enter. But if it is an array formula then we need to press Ctrl + Shift + Enter after typing the formula. Hope you will get this now.

[Glenn Kennedy]

Im away for about half an hour....

[Glenn Kennedy]

That is because you did not change the ranges that the formulae were looking at. To remove the need to do that, I have set up a Named range (called nominal, hit CTRL F3 to see the formula in use there), that will adjust the range automatically as new rows area added (currently to a maximum of 1000).

I have no idea what should go into the yellow cells in sheet C as the headings don't exist in sheet A.

[cuty]

Thank you so much xx
it's perfect!!!

A million thanks once again!

[Glenn Kennedy]

You're welcome and Thanks for the Rep!!