comparing three values which are changeable, Excel Formula

[DavyJones90]

Hello everyone!

Need your help again.

What I want to do.

3 different worksheets need to be compared, within these three worksheets, there are three cells with strings, I need to check if there is one value but not another, within these three strings and if at least 2 of the three match

=IF(IF(ISNUMBER(SEARCH("↘",Z10)),"↘",1)+IF(ISNUMBER(SEARCH("~?",Z10)),"?",1)="↘?","next formula","↘")

This is what I got so far, but this will only give me if both strings are there in one of the three workbooks. What I need is that these 3 are now compared and ensured that the first part exists but not the second if the second exists and the first, the next formula needs to be applied

Workbook1	Workbook2	Workbook3
↘	↘?	↘(D)

These should give back the value ↘ because both 1 and 3 have a down arrow and only 2 has down arrow questionmark.

 

[cmowla]

I'll wait to see if this is potentially a better option for you to continue with than cmowla's suggestion before doing so.

I gave the solution I did, because I assumed that over time, the symbols in the cells to compare may change, and my formula allows him to not have to manually modify the formula when they do. And in the original post, he was asking for someone to create a formula which compares in a general sense. Hence, my answer.

In addition, it appears that he's not going to drag this formula down. (He's just comparing 3 sheets, not rows on 3 sheets.) That it's just to be put into one cell. So, even though the formula is long, it doesn't really matter.

Therefore, from my above understanding, I don't understand why you are actively trying to persuade him to not use my formula. I started working on this solution the same time that you did, even though you posted (a question, not a solution) first, by the way. And it's not like I didn't take the time to explain how my formula works. (It surprised me that even after my explanation, you called the formula "convoluted". I'm sure you understand how LET works, and therefore you can see that the formula is well-organized and broken into manageable chunks to understand.)

But that's all I have to say about this thread.

 

[jasonb75]

I don't understand why you are actively trying to persuade him to not use my formula.

Where have I been doing that? We have interpreted the requirement in different ways and I have merely offered an alternative based on my interpretation of the requirement. If you read the last line of my previous post properly then you will notice that I have said the formula still needs work if it is preferable for them to use instead of yours. In my opinion, I think that they will stick with yours based on their replies but that remains to be seen.

 

[cmowla]

Where have I been doing that?

I made it quite obvious. (I quoted the statement you made which implies that. And there were several similar statements made throughout this thread. That's just the most recent.)

We have interpreted the requirement in different ways

The original requirement made no mention of assigning values to the symbols.

And from the original post:

This is what I got so far, but this will only give me if both strings are there in one of the three workbooks. What I need is that these 3 are now compared and ensured that the first part exists but not the second if the second exists and the first, the next formula needs to be applied

Workbook1	Workbook2	Workbook3
↘	↘?	↘(D)

These should give back the value ↘ because both 1 and 3 have a down arrow and only 2 has down arrow questionmark.

I assumed the symbols could vary in the future, hence why I wrote a formula for the general case. If you mean that you interpreted it as he just wants to compare those specific symbols (and nothing else), such that you could create a shorter formula to just do those specific select data, then yes, we interpreted the requirement differently. But if my formula does that (and more), why do you think that he would prefer a formula that does less? (Again, especially that he's not going to carry the formula down, and, again, because the symbols and the quantity of symbols could change in the future.)

 

[jasonb75]

I made it quite obvious. (I quoted the statement you made which implies that.)

Then you clearly don't understand plain English, so let me simplify the line that you quoted. "I will wait for the OP to review my suggestion and decide whether or not they think that it is better suited to their requirement than the suggestion posted by cmowla."

I assumed the symbols could vary in the future,

Assumption often leads to error which is why I asked for clarification on parts of the requirement before throwing suggestions that are likely to be useless.

If you had taken the time to read the replies to the the questions that I asked of the OP then you would have found that your formula returns common matches where one is not found.
In the examples provided by the op, the strings in workbook 2 and workbook 3 are not a match in the mini sheet shown below, yet your formula says that they are. My formula shows a blank because there is no common match found.
There may be other similar problems with your formula and different test strings, this was simply the one that stood out like the proverbial sore thumb when I first looked at the mini sheet in your post. If your suggestion had worked as the OP had asked then I wouldn't have thought it necessary to work on a more accurate alternative.

Not that I'm saying that my suggestion is any better than yours, it addresses the problem with your formula but there may be other cases where it fails (apart from blanks, we already know that I haven't addressed that) that I have not yet noticed.

Book1
	A	B	C	D	E
1	Workbook1	Workbook2	Workbook3
2	R	↘?	↘(D)	↘
Sheet3

Cell Formulas
Range	Formula
D2	D2	=LET( k_1,MID(A2,SEQUENCE(LEN(A2)),1),k_2,MID(B2,SEQUENCE(LEN(B2)),1),k_3,MID(C2,SEQUENCE(LEN(C2)),1), k1k2,IFERROR(MMULT(IFERROR(SEARCH(k_1,TRANSPOSE(k_2)),0),0*SEQUENCE(ROWS(k_2))+1),0), k2k3,IFERROR(MMULT(IFERROR(SEARCH(k_2,TRANSPOSE(k_3)),0),0*SEQUENCE(ROWS(k_3))+1),0), k3k1,IFERROR(MMULT(IFERROR(SEARCH(k_3,TRANSPOSE(k_1)),0),0*SEQUENCE(ROWS(k_1))+1),0), relationships,CONCAT(k_1&","&k1k2&"|")&CONCAT(k_2&","&k2k3&"|")&CONCAT(k_3&","&k3k1&"|"), arr,TEXTSPLIT(LEFT(relationships,LEN(relationships)-1),"|"), incommon,IF(IFERROR(SEARCH(",1",arr),0)>0,LEFT(arr,LEN(arr)-2),""), IFERROR(CONCAT(UNIQUE(FILTER(incommon,incommon<>""),1)),"nothing in common") )
E2	E2	=IFERROR(LET(arr,CHOOSE({1,2,3},A2,B2,C2),ref,{"↗(M)","↗(D)","↗","↗?","R","-","↘?","↘","↘(D)","↘(M)"},val,{1,1,1,0.5,0,0,-0.5,-1,-1,-1},l,XLOOKUP(arr,ref,val,""),TEXTJOIN(",",1,IF(l=MODE(l),arr,""))),"")