How to return column title from an array formula

Hi everyone.

I have a table of results from a form giving me a number of items and whether they are missing or present. The Items are in f1:BM1 and the result (missing or present) are in f2:bm2. I want a list created in cells a4:a(however many results say missing). I found a formula online to do this sort of thing but it is an array for rows and not columns and no matter how much I try and play with it I can't get it to do what I want it to do.
The formula I got online is

Please Login or Register  to view this content.

I have modified it to fit my parameters and seems to recognised the "Missing" part as when I evaluate it it returns a value for each column number so for F it returned 6 then false for G and then 8 for H and so on

Please Login or Register  to view this content.

To be honest I don't understand the array formula well enough to work out the problem.
If anyone can help I'd be much appreciated

Neil

Try

in A4

=IFERROR(INDEX($F$1:$BM$1,SMALL(IF($F$2:$BM$2="missing",COLUMN($F$1:$BM$1)-COLUMN($F$1)+1,""),ROWS($1:1))),"")

...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.

then copy down

Try this in A4:

=IFERROR(INDEX($1:$1,SMALL(IF(F$2:BM$2="missing",COLUMN(F$2:BM$2)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula down as far as needed.

Thank you so much. That works perfectly.

Glad we could help. Thanks for the rep!