Problem with getting CONCATENATE text combined with an IF/THEN and nested VLOOKUP to display...

1. I just remembered an earlier issue you had noticed that I didn't understand how to correct that should greatly decrease this workbook's size and I would like to enact the change.

It had to do with you having noticed a range that went all the way to the last bottom row of the 01 - v2.22 sheet. You had suggested that by using CTRL + "down arrow" I would arrive at the last cell in that range. You were right, the cursor did end up in that last cell. I don't know how to delete or clear or "un-rangeify" the undesired cells. Can you describe the process for me when you have a moment?

Thank you again for your insights. You have been a tremendous help!

Douglas997t  As mentioned above replace U12>0 with ISNUMBER(U12) as shown on lines 44 & 53 below :

=IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="SWAG"),
   CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
               VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10),
               VLOOKUP($T$12,$W$281:$X$285,2)
   ),
   IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="SWAG"),
     CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                 VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10),
                 VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10),
                 VLOOKUP($T$12,$W$281:$X$285,2)
     ),
     IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="Stair Step"),
        CONCATENATE(VLOOKUP($G$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                    VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10),
                    VLOOKUP($T$12,$W$281:$X$285,2)
        ),
        IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="Stair Step"),
          CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                      VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10),
                      VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10),
                      VLOOKUP($T$12,$W$281:$X$285,2)
          ),
          IF(AND($B$12="Cat. 2",$C$12="N/A"),
            CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                        VLOOKUP($H$12,$W$107:$X$116,2)&CHAR(10)&CHAR(10),
                        VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10),
                        VLOOKUP($T$12,$W$281:$X$285,2)
            ),
            IF(AND($B$12="Cat. 2",$C$12="B-Alert!"),
              CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                          VLOOKUP($I$12,$W$123:$X$132,2)&CHAR(10)&CHAR(10),
                          VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10),
                          VLOOKUP($T$12,$W$281:$X$285,2)
              ),
              IF(AND($B$12="Cat. 3",$C$12="N/A"),
                CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                            VLOOKUP($H$12,$W$156:$X$165,2)&CHAR(10)&CHAR(10),
                            IF(S12>0,
                              VLOOKUP($S$12,$W$304:$X$313,2),
                              ""
                            )&CHAR(10)&CHAR(10),
                            VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10)&CHAR(10),
                            VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10),
                            IF(ISNUMBER(U12),
                              CONCATENATE("P.S., ",VLOOKUP($U$12,$W$290:$X$299,2)),
                              "")),
                IF(AND($B$12="Cat. 3",$C$12="B-Alert!"),
                  CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                              VLOOKUP($I$12,$W$172:$X$181,2)&CHAR(10)&CHAR(10),
                              IF(S12>0,VLOOKUP($S$12,$W$304:$X$313,2),""),
                              VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10),
                              VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10),
                              IF(ISNUMBER(U12),CONCATENATE("P.S., ",
                                       VLOOKUP($U$12,$W$290:$X$299,2)),
                                       "")
                              ),
                  "")
                )
              )
            )
          )
        )
      )
    )

To clarify my last question I have included (2) screen captures.

Screen Capture #1: eABCResponse-b_PS-01_Image1of2_2023-08-24.jpg

Notes...Screen Capture #1 reflects when U12 contains a value. Rather than describe what the boxes and arrows should pretty well illustrate, check out the image(s) instead and let me know if you have any additional clarification.

Screen Capture #2: eABCResponse-b_PS-02_Image2of2_2023-08-24.jpg

Notes...Screen Capture #2 reflects when U12 is empty

You will see that whether U12 contains a value or not the P.S. remains. As mentioned in my little text box notes in both images, the P.S. should disappear when U12 is empty. And both the P.S. and the short sentence should appear together when U12 contains a value. I can't figure out how to get it to do this. And, if it can't so be it, I will have to delete the P.S. when pasting to an email body text. It would be faster to use if it did what I have in mind. I am pretty sure it is an order of operation issue but a number of attempts at fixing have yielded no gain.

As usual, I appreciate your guidance on this!

The addition of the closing parens worked perfectly. I really appreciate your endless diligence and willingness to work through this with me.

I will keep working through a sample sheet using the other solution you provided with a migration in mind in the near future. At least this gets this functionality up and running for now.

One aside here. Can you think of a way to get the "P.S., " to disappear when U12 is not selected? It has become a static feature and I can live with deleting it as needed but would love to find a way to have it disappear when no needed. I can see it is caught within a CONCATENATE and probably why is doesn't adhere to the If/Then " " ending command. Any ideas?

Enjoy the upcoming weekend!

Douglas

Douglas997t I think a much better solution would be to have 1 Table (let's format as a table and call it Responses) and then you just use FILTER or lookup on that single table.  I imagine something like:

=FILTER(Responses[text], (Responses[Category]=$B$12)*(Responses[Sub-Cat]=$C$12)*(Responses[SWAG]=$D$12)*(Responses[Choice]=$T$12),"")

now it might still need a couple tables or more logic in that filter but I think that could help.

as for the problem here I was mistaken slightly and I think you need only another ) after that first "here" to close the CONCATENATE. I got rid of the "here" notes and just left the formula I think you want in the attached.

Douglas997t Actually I do get what you are trying to do and I think it is me that is not explaining the problem clearly.  The problem is that you have IF() and CONCATENATE() statements that overlap each other and it reaches that first IF(AND($B$12="Cat. 3",$C$12="N/A") which is FALSE and then everything else is skipped and eventually reaches the bottom )))))) and finds no FALSE condition so it returns FALSE.  Here is sort of how I SEE the groupings of that formula.  Notice how after that first IF with Cat. 3 all the rest is indented even more.  I could take a GUESS at the places I THINK you want to end the groupings but afraid it may just make it look like it works for now until later you find another error because I don't know what you really want.  So instead I hope this shows you how you NEED to critically inspect all those groupings and why the above members complained and warned about this sort of style, it get very hard very quick and very very very easy to make a mistake.  That all said I suspect the location of my first "here" (line 46), should actually be "here")),""), to also end the CONCATENATE and that prior IF and then a latter "and here")) would get deleted.

=IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="SWAG"),
   CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
               VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10),
               VLOOKUP($T$12,$W$281:$X$285,2)
   ),
   IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="SWAG"),
     CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                 VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10),
                 VLOOKUP($F$12,$W$74:$X$84,2)&CHAR(10)&CHAR(10)&CHAR(10),
                 VLOOKUP($T$12,$W$281:$X$285,2)
     ),
     IF(AND($B$12="Cat. 1",$C$12="N/A",$D$12="Stair Step"),
        CONCATENATE(VLOOKUP($G$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                    VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10),
                    VLOOKUP($T$12,$W$281:$X$285,2)
        ),
        IF(AND($B$12="Cat. 1",$C$12="B-Alert!",$D$12="Stair Step"),
          CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                      VLOOKUP($I$12,$W$59:$X$68,2)&CHAR(10)&CHAR(10),
                      VLOOKUP($G$12,$W$90:$X$99,2)&CHAR(10)&CHAR(10)&CHAR(10),
                      VLOOKUP($T$12,$W$281:$X$285,2)
          ),
          IF(AND($B$12="Cat. 2",$C$12="N/A"),
            CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                        VLOOKUP($H$12,$W$107:$X$116,2)&CHAR(10)&CHAR(10),
                        VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10),
                        VLOOKUP($T$12,$W$281:$X$285,2)
            ),
            IF(AND($B$12="Cat. 2",$C$12="B-Alert!"),
              CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                          VLOOKUP($I$12,$W$123:$X$132,2)&CHAR(10)&CHAR(10),
                          VLOOKUP($J$12,$W$139:$X$148,2)&CHAR(10)&CHAR(10)&CHAR(10),
                          VLOOKUP($T$12,$W$281:$X$285,2)
              ),
              IF(AND($B$12="Cat. 3",$C$12="N/A"),
                CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                            VLOOKUP($H$12,$W$156:$X$165,2)&CHAR(10)&CHAR(10),
                            IF(S12>0,
                              VLOOKUP($S$12,$W$304:$X$313,2),
                              ""
                            )&CHAR(10)&CHAR(10),
                            VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10)&CHAR(10),
                            VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10),
                            IF(U12>0,
                              CONCATENATE("P.S., ",VLOOKUP($U$12,$W$290:$X$299,2)),
                              "here"),
                            IF(AND($B$12="Cat. 3",$C$12="B-Alert!"),
                              CONCATENATE(
                                  VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
                                  VLOOKUP($I$12,$W$172:$X$181,2)&CHAR(10)&CHAR(10),
                                  IF(S12>0,VLOOKUP($S$12,$W$304:$X$313,2),""),
                                  VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10),
                                  VLOOKUP($T$12,$W$281:$X$285,2)&CHAR(10)&CHAR(10),
                                  IF(U12>0,
                                    CONCATENATE("P.S., ",
                                       VLOOKUP($U$12,$W$290:$X$299,2),""),
                                    "and here")
                              ),
                             "and here2")
                ),
                "and here3")
              )
            )
          )
        )
      )
    )

We do seem to be looking at this from two very different perspectives from the stand point of what is attempting to be accomplished by this chunk of formulas. I might be able to compile a more detailed explanation to help you better understand but if there is any way we could do this through SKYPE, Phone or other communication modality, I can get you straight on all of what is to follow in less than 3 minutes...just sayin'!

What you replied is correct in some ways and does get rid of the FALSE but that is only half of what is needed. The chunk defined in B12 as "Cat. 3" and in C12 reflects the value "B-Alert! and has a value of "1" showing in your workbook. This correlates with the last chunk of formulas shown below;

=IF(AND($B$12="Cat. 3",$C$12="B-Alert!"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($I$12,$W$172:$X$181,2)&CHAR(10)&CHAR(10),
IF(S12>0,VLOOKUP($S$12,W304:X313,2),"")&CHAR(10)&CHAR(10),
VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,W281:X285,2)&CHAR(10)&CHAR(10)&CHAR(10),
IF(U12>0,CONCATENATE("P.S., ",VLOOKUP($U$12,W290:X299,2),""))))

I took this from the smaller merged cell box in O17:U36. The sentences that appear in this box are a conglomeration of individual sentences found within various ranges in column W and each formula within this group relates to a different range in that column and through a VLOOKUP, pulls the selected (selected by choosing the appropriate dropdown from the range E9:U9) sentence. In this example when you look to the range E12:U12, you will see the values there match those selected in E9:U9. E12=E9...The reason it is done this way is for tracking on sheet "01 - v2.22" but we don't need to go there for this purpose.

When B12=Cat. 3 AND C12=B-Alert! + the appropriate selections have been made to compile the paragraph we need by selecting from the appropriate dropdowns in row 9 the combination will/should look like what you find in the merged cell range E9:U9 smaller right side orientation rather than the larger range against column "A". It has become a copy/paste worthy paragraph I can send out and it is compiled by the data and calculations that exist in sheet 01 - v2.22. Again, you don't really need to know the entire structure of this workbook to solve the issue I am having.

1. The chunk of formulas that I placed a paragraph or two above this works beautifully in this smaller range.

2. In the larger merged cell chunk of formulas in B17:M43, there are 7 or 8 groupings or chunks of formulas, each is geared toward a different combination of variables held in B12 & C12 as well as others in row 9 moving toward column U. Focus only on B12 & C12 for now.

3. The previously inserted formula chunk is the last of the 8 groupings held in B17:M43. Each grouping has an open Parenthesis and a close Parenthesis and is called upon to both pull and insert the verbiage contained in the various VLOOKUPs held in their own associated groups in column X.

4. All combinations of B12 & C12 options work perfectly within B17:M43 except the last one which is defined in B12 (Cat. 3) & C12 (B-Alert!). See below for another copy of that formula chunk...
=IF(AND($B$12="Cat. 3",$C$12="B-Alert!"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($I$12,$W$172:$X$181,2)&CHAR(10)&CHAR(10),
IF(S12>0,VLOOKUP($S$12,W304:X313,2),"")&CHAR(10)&CHAR(10),
VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,W281:X285,2)&CHAR(10)&CHAR(10)&CHAR(10),
IF(U12>0,CONCATENATE("P.S., ",VLOOKUP($U$12,W290:X299,2),""))))

The above chunk should pull the following English body text with the use of the VLOOKUPs embedded within each coordinating formula chunk held in B17:M43 as determined by largely the values that show up in B12 & C12 as follows;

"Thanks, appreciate the information.

We would love to help you move this property off your books. Unfortunately, the expense numbers are way below industry norms. Without a very substantial discount we won’t be writing an offer today.

In the meantime, we still have a chunk of capital that needs to be placed. Maybe you have other assets with more aggressive returns?

Douglas

P.S., When do you need to close by?"

In each case where the values in B12, C12 and the entire range of B9:U9 the formulas held in B17:M43 will react differently displaying the English body text associated with what each chunk tells it to pull leaving me with a similar looking format to what you see in O17:U36. As of right now the formula chunk in O17:U36 is dedicated to B12=Cat. 3 AND C12=B-Alert! You can check the results we get in B17:M43 against what shows up in O17:U36 and if the underlying formulas in B17:M43 are structured properly they should match under the same B12 & C12 conditions. The end result in B17:M43 MUST look identical to the group in O17:U36 which was just placed there as a testing ground for each formula grouped together in B17:M43 before moving it across and compiling them into the larger chunk. This formula **bleep** is move across by highlighting it in the formula window then copying through the formula window to the appropriate place in B17:M43.

Something is wonky with that last formula grouping causing it to display FALSE rather than the paragraph body text as reflected above. I have been troubleshooting it for a couple of days and don't see where there is any difference between the two formulas and need assistance to find the deviation in closing brackets, an extra "," etc.

Keep in mind that all other combinations reflected in other groupings within this range work perfectly so focus only on this last grouping.

I know this seems like, as one responder put it, a hornets nest of formulas and formatting and it of course IS exactly that. It is a substitute to having a far less flexible app programmed outside of Excel and is actually a mini-CRM with tons of automation that speeds up our processes by a factor of 10 over the manual input method that is really the only alternative short of a full blown program development. Just want you to better understand what you are looking at. And, try your best not to get into a macro POV on this, it is a very small change that needs to be made to get this section to work, not a major re-design.

Sorry for the elongated reply but it was becoming clear you were not understanding what I am trying to accomplish here and a longer-winded explanation seemed the only non-phone call way for me to help you to help me. (Jerry Maguire reference)

Thanks in advance...Douglas

Douglas997t as I mentioned you should move the cursor to each close parentheses and check if that is still on the TRUE argument and missing a FALSE argument.  I did that and found a number of cases missing the FALSE.  In this case your FALSE output was where I entered "and here3"

I also ran the attached through that optimizer I mentioned and the good news is it found tons of things to optimize (mostly formatting and such so you may not like some of those optimizations) and the bad news is that is made very little difference.  Either way, the above tweaked formula showing you all those locations is in there.

On sheet "03 - eABC Response" (New full workbook included), I corrected a workflow issue by moving the column marked "P.S. Final Confirmation" that was in T11 to S11 which in turn moved the column in S11 "Signature" to T11. From a formula perspective I also changed the area we were working on to some degree and in the process, found a way to do what I intended in a test merged group O17:U36 and inserted the new version of that formula grouping as;
=IF(AND($B$12="Cat. 3",$C$12="B-Alert!"),
CONCATENATE(VLOOKUP($E$12,$W$34:$X$53,2)&CHAR(10)&CHAR(10),
VLOOKUP($I$12,$W$172:$X$181,2)&CHAR(10)&CHAR(10),
IF(S12>0,VLOOKUP($S$12,W304:X313,2),""),
VLOOKUP($J$12,$W$188:$X$197,2)&CHAR(10)&CHAR(10),
VLOOKUP($T$12,W281:X285,2)&CHAR(10)&CHAR(10)&CHAR(10),
IF(U12>0,CONCATENATE("P.S., ",VLOOKUP($U$12,W290:X299,2),""))))

It does everything including the insertion of the "P.S., ". I then copied and overwrote the same element of the larger formula held in the range B17:M43 where after adjusting the closed parenthesis to what appears to be the appropriate number, get a "FALSE" answer. Can you take a look to see if you can figure out why it happens please. I am coming up blank.

I have included (3) screen captures to better illustrate the formula in the test group and when moved by highlighting from the formula bar, copying/pasting in the other range of merged cells I mentioned earlier. It is spaced out for easier viewing. I have also included the updated full workbook that includes these newest alterations and the "FALSE" error.

I should add that all formulas in the merged cell group B17:M43 work properly, except for this last section we'll refer to as "Cat. 3 / B-Alert!" as this is the top line of this cluster and is differentiated from the clusters by that title (although part of the formula, you get the idea)

Thanks for the 2nd look...

mtarler 

Afraid blank as a value requires significant Excel calc engine update. Not sure it will be ever done, will see.

THAT is very interesting. Makes me wonder what Excel "sees" when it sees ""
I've always assumed that was emptiness....so I suppose they could call that desired function either EMPTY() or BLANK() but, whatever, I second your motion.

Or to stay with your far more colorful illustration PUNCTUREDBALLOON()

I appreciate your response and am considering an alteration to the track that formula was created to solve. It should work much better with the new version once dialed in. As for the "" v " " conversation, I understand what you are saying and couldn't agree more. I asked not because I couldn't see the physical difference but because the change in the way the " " related to the function of the formula. Clearly it changed the way the formula interacted with the underlying data and that is what I was curious about. I used to write If/Then using =IF(A1>B1, B1, " "). After some trial and error with the two versions I concluded there was no difference under most circumstances. This exercise changed that. That is why the question of how they are different. Only in terms of how the interjected space is interpreted by Excel and thus how it changes the way I might approach creation of future formulas and my choice to either go with "" or " ". I will reach back out once I have a chance to work through the changes I mentioned earlier.

Thanks again for all of your help and guidance!