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
=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.
The result in HR250 looks like this
NAME A H10 |
NAME B H10 |
NAME C H8 |
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.