I’m currently tackling a challenge in my spreadsheet. I need to format employee names based on certain conditions. I have a working formula that identifies names connected to either ‘H’ or ‘V’, but I also want to append an ‘(H)’ next to the name if ‘H’ is present.
To clarify, this is how I want it to work:
If a cell displays ‘H’, I want the output to appear like this: Employee1(H), Employee2
If it doesn’t contain ‘H’, the output should just be: Employee1,
Here’s my current formula that brings together the names:
It seems to only return the names without any markers. I’m unsure why the IF condition never seems to evaluate to true. Can anyone assist me in figuring out how to successfully append ‘(H)’ to the relevant names?
The problem lies in your use of the IF function with array ranges directly, as Google Sheets does not support array conditions in this manner. You should utilize ARRAYFORMULA for your conditional formatting. Try implementing the following formula:
This will effectively filter names and conditions from column K first, and then with ARRAYFORMULA, it appends “(H)” to names associated with the “H” mark.
you’re overcomplicating this. use MAP instead - it handles conditional formatting per row way better than nested IFs. try something like =IFNA(JOIN(", ", MAP(FILTER(Input!$A$5:$A$30, REGEXMATCH(Input!K5:K30, "V|H")), FILTER(Input!K5:K30, REGEXMATCH(Input!K5:K30, "V|H")), LAMBDA(name, status, name&IF(status="H", "(H)", ""))))) - should work better.
Your formula has a basic problem with the conditional logic. Don’t try to separate H and V cases - build the conditional formatting into one operation that handles all filtered results together.
This filters both the names and K column values based on your V|H criteria first, then uses IF within ARRAYFORMULA to add “(H)” only where it’s needed. Keeps everything in order and makes sure all qualifying names show up with the H marker applied correctly.