Formulas wile working with few sheets information

[Norman_Glenn]

Hi,,, below is an example of a work book I'm using, In this example I'm using 2 work sheets but normally it's more, in work sheet 1 I have information and in column 1 I have a formula been used, in sheet 2 I have filled information from sheet 1, you may see I have coloured them using conditional formatting, to the right on sheet 2 I want to creat a formula that will pull the correct iteams under the correct section,colour, I'm using, in cell I22 I'm puting   =if(A3="WT C",A3:A4,"")    when I enter this it returns nothing, well nothing that I can see, no error no box saying I'm doing it wrong, 

[#2]

@Norman_Glenn On the items worksheet, the formula in column A is returning an extra space character at the end of the text string. For example, the first part of the formula in cell A2 is =B2&IF(logical_test," C "," P "), which returns "WT C ".

Change the formula in cell I22 on the Carry Over Page worksheet to either of the following:

=IF(A3="WT C ",A3:D3,"")

//OR

=IF(TRIM(A3)="WT C",A3:D3,"")

[#3]

@Norman_Glenn 

In addition, since you use formulae like =if(A3="WT C",A3:A4,"") it looks like you are on Excel 365, or at least 2021. If so why don't to use dynamic arrays more extensively, e.g. fo the beginning in I22

=IFERROR(
    FILTER(
        CHOOSECOLS('items '!$A$2:$Q$600, {1,3,4,17} ),
        'items '!$A$2:$A$600 = $I$20 ), "" )

and so on

[#4]

So far your reply works best on my phone, tomorrow ill be trying it on my sheet on my work book and I'll let you know

[#5]

I tried the trim formula and it carried over 2 difrent options in stead of the one I was looking for but I'll try it tomorrow in my spread sheet and see how it works

[#6]

@Norman_Glenn Just make sure the value_if_true argument is referencing the correct range or cell that you want to be returned (in the original question above, you've referenced A3:A4, but the formula in cell I22 of the spreadsheet you shared is referencing range A3:D3).

As stated by @Sergei Baklan, the FILTER function is probably what you should be using for this scenario. If this is the first time you're hearing about this function, you might want to research it first to get a better understanding of how it works. Some online resources include:

• Microsoft: https://support.microsoft.com/en-us/office/filter-function... 
• ExcelJet: https://exceljet.net/functions/filter-function 
• YouTube: Excel Lookup to Return Multiple Values with FILTER Function 
• YouTube: Excel FILTER Function + Trick to Rearrange Column Order 

To reiterate, the source of your original problem with the IF function was the presence of a trailing space character in one of the text strings being compared ("WT C " does not equal "WT C"). Just be aware that any formula or lookup function that compares one value to another (ie: FILTER, XLOOKUP, XMATCH, COUNTIFS, etc.) will all suffer the same fate if leading or trailing spaces exist in one value but not the other. Cheers!

[#7]

@djclements and @Sergei Baklan , Thank you for your reply,  yes it is ment to be A3:D3, in my sheet their is ment to be a space "WT C" "WT P" is having this space ok or should I try dropping it, I have attached a striped back sheet with both fourmla in it, @djclements as you may see I use your formula and the cell just looks blank, must be ok tho as its not returning an error, with @Sergei Baklan I have also used your formula, thing with this one is it seams to count down column A till it finds what it looking for and then when transferring it over it uses the same amount it counted down to input answer, any other help with them,

[#8]

@Norman_Glenn 

It's better to restructure data a bit, but it depends on its logic. In any case you may start with dynamic formulae for intermediate calculations as well. 

Tube Size could be return for entire column at once

=LET(
    nRows,  ROWS( TOCOL($B:$B,1) ),
    size, B2:INDEX(B:B, nRows+1),
    colour, C2:INDEX(C:C, nRows+1),
  TRIM( size
    & IF( ISNUMBER(SEARCH("CARDBOARD",colour))*
     (
        (size="WT")+
        (size="NT")+
        (size="ET")+
        (size="MT")
     )," C ", " P ")
    & IF( ISNUMBER(SEARCH(" NG",colour))*
     (
        (size="WT")+
        (size="NT")
    ), "NG","")
) )

repeat report data to Carry Over Page with

=LET(
    nRows, ROWS('Past Buggy Report'!$A$2# ),
    CHOOSECOLS(
    'Past Buggy Report'!$A$2:INDEX('Past Buggy Report'!$Q:$Q, nRows+1 ),
    XMATCH({"Tube Size","Tube Colour","Stock No","Count"},
           'Past Buggy Report'!$A$1:$Q$1)

) )

when filter will be as

=IFERROR( FILTER($A$3#, CHOOSECOLS($A$3#,1)=$I$1), "" )

Similar with other formulae.

[#9]

@Sergei Baklan thank you for your reply but I'm having bother, I have tried using your fourmla as you have and each time I keep getting "Spill" even tho I am copping your fourmla exactly and iv compared both and are both the same. Any ideas.  Bellow I have attached the sheet,

[#10]

@Norman_Glenn 

#SPILL! error

if it's not enough space (range wit blank cells) to expand the data by formula.

If we check next row(s)

there are some formulas or data in the next cells. Select and clean (Del) them to empty the space for the dynamic array, it works now