Excel concat function

Question

I need a little bit of help. I'm trying to create a macro. It's for hotel vacancies.

Column F2:F8 lists vacancy or occupied. Column A2:A8 lists the room numbers.

I'm trying to create a macro that will list the room numbers in a different cell, if their corresponding room is vacant. I'm truly grateful for all your help!

=concat((a2:a8)If(f2:f8,"vacant"))???

Answer 1

use TEXTJOIN as it will allow the addition of a delimeter in an array form:

=TEXTJOIN(", ",TRUE,IF(F2:F8 = "vacant",A2:A8,""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.