I have two sets of two similar formulas that produce INDEX outputs from the same set of data. The first set of formulas are in H3:H9 and H12:H19 and the second set are under the dataset in A30:A36 and A37:A43.
=iferror(INDEX(B$2:B$26,SMALL(IF($E$3:$E$26="Scheduled",ROW(E$3:E$26)-ROW(E$1)), ROWS(B$2:B2))), "")
This formula takes the first seven rows that contain "Scheduled" in Column E and puts it in a list. If there are less than seven, it leaves blank cells. Then there's an otherwise identical formula that does the same thing but with "In Progress" below it.
=iferror(CONCATENATE(INDEX(B$2:B$26,SMALL(IF($E$2:$E$26="Scheduled",ROW(E$2:E$26)-ROW(E$1)), ROWS(B$2:B2)))," - ",INDEX(C$2:C$26,SMALL(IF($E$2:$E$26="Scheduled",ROW(E$2:E$26)-ROW(E$1)), ROWS(B$2:B2))), ""),"")
This formula does basically the same thing as the one above but adds the contents of Column C to it. Again there's another formula that does the same thing, but with "In Progress" below it.
Because of the complexity of my real worksheet, I can't make Column H any wider, and so the names in these cells overflow and they're not readable. I need to change all four of these formulas to replace the outputs with shorter versions of the names so that they fit in Column H. The names are always the same.
How do I replace specific names I know are too long with shorter versions that I've created?
There's a table at the bottom of my sample spreadsheet that lists the names that are too long and what I want to place them with.
I think the SWITCH function is what I'm looking for, but I'm not sure how to incorporate it into my current formulas.