-1

Here is a sample spreadsheet I threw together that shows more or less what I'm working with.

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.

0

Unless I'm missing something big, Why not just use VLOOKUP or the INDEX, that you manipulated so much ( which also need not be done in Google sheets)

 =ARRAYFORMULA(iferror(VLOOKUP(H3:H9,B46:D51,2,0),H3:H9))

You could also simply use the TEST WRAP feature of Google sheets.

Here's a uncalled simplification of H3:

=array_constrain(FILTER(B2:B26,E2:E26="Scheduled"),7,1)

EDIT:
Considering OP's cry for extra help which is not mentioned in the Question,

A30:

  =ARRAY_Constrain(FILTER(IFERROR(VLOOKUP(B2:B26,B46:D51,2,0),B2:B26)&"-"&C2:C26,E2:E26="Scheduled"),7,1)
  • That's definitely a much simpler way to get the same result I did. I replaced what I had with your version. Your first formula absolutely gives me the result I want to see, but the problem is that I need that result to display in H3:H9 as well as A30:A42 with the data from Column C added to it. Preferably, all this without the need for a table at the bottom like I do currently. This is why I initially thought SWITCH might do what I want since it has every case and value inside of the formula rather than being visible on the spreadsheet. – Gnrl Kitty Sep 15 '17 at 6:24
  • Do you want only 7 elements? – TheMaster Sep 15 '17 at 6:33
  • Yes, always seven. – Gnrl Kitty Sep 15 '17 at 6:36
  • 1
    Thank you so much. This did exactly what I didn't know how to do. Although I don't know what you mean by crying for extra help that wasn't mentioned in the question. Regardless, I'm extremely grateful. – Gnrl Kitty Sep 15 '17 at 7:10
  • 1
    Your question was "How do I replace specific names I know are too long with shorter versions that I've created?",Which I did already answer.I,and in general, all of Stack Overflow, don't like it,When you change Goal posts like that ( or ask a follow up question). You should've mentioned that you also need to concatenate that info. The question and all your needs should be put out front at the beginning.The reason being- When we provide a solution, It's not easily adaptable to your second need and your third need.Had you mentioned you just need A30,I would've simply provided the last solution. – TheMaster Sep 15 '17 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.