Dynamic arrays

[Kariba]

I have an array that pulls names from column C plus a value from column HR if column D does not contain 'Nights' or HQ if it does contain nights. The values in HR/HQ etc are matched from a named range called 'Holiday'

The result in HR250 looks like this

NAME A H10

NAME B H10

NAME C H8

=IFERROR(VSTACK(
FILTER($C5:$C210 & " " & INDEX(Holiday, MATCH(HR5:HR210, Holiday, 0)), (ISNUMBER(MATCH(HR5:HR210, Holiday, 0))) * (ISNUMBER(SEARCH("Nights", $D5:$D210))=FALSE)),
FILTER($C5:$C210 & " " & INDEX(Holiday, MATCH(HQ5:HQ210, Holiday, 0)), (ISNUMBER(MATCH(HQ5:HQ210, Holiday, 0))) * (ISNUMBER(SEARCH("Nights", $D5:$D210))))
), ""NAME )

The issue is when I drag the array to other cells columns HS and HT are blank and then values from HU onwards again. Formulas are correct. There are values in HS & HT, names are correct, nights etc are correct. I just can't work out when it works perfectly on the columns before and after.

 

[StephenCrump]

Your formula in HR250 refers to columns HQ and HR. If you copy the formula to HS250, it will refer to columns HR and HS.

I am guessing that this is incorrect, and that the next formula should perhaps refer three columns to the right, i.e. to columns HT and HU? Are the results in HU250 correct?

It's relatively simple to have a formula increment by N (>1) columns when dragged to the right. But it probably makes more sense here to align the formulae with the relevant columns.

Your formula in HR250 can be shortened to:

=IFERROR(TOCOL($C5:$C210&" "&VLOOKUP(IF(ISNUMBER(SEARCH("Nights",$D5:$D210)),HQ5:HQ210,HR5:HR210),Holiday,1,),2),"NAME")

If I'm correct about the column spacing, you could change this to:

=IF(MOD(COLUMNS($HR250:HR250)-1,3),"",  IFERROR(TOCOL($C5:$C210&" "&VLOOKUP(IF(ISNUMBER(SEARCH("Nights",$D5:$D210)),HQ5:HQ210,HR5:HR210),Holiday,1,),2),"NAME"))

and drag to the right.

 

[Kariba]

Hi, the formula did work correctly as intended in every cell apart from columns HR and HS when dragged across. It should be picking up anything <> 'nights' in column HR and = 'nights' in HS. I've just discovered that there was no entries to match to 'nights' in 'holiday' range in HQ & HR so wasn't returning any results at all with VSTACK, but did if I separated the two.

Will change the divisor to 1 and check results of yours against the separated results.

Thanks for your help

 

[Kariba]

The data output all seems correct so far. I'll still need to work out how you did it, but at least we have the option of either separated days/nights or your stacked version.

 

[StephenCrump]

That sounds encouraging. Please post back if you run into problems.