Excel spilling formula by the size of a reference list-CodePudding

I am trying to build a formula that will look for 3 possible values in 3 different cells. I have a table with a fix structure that I cannot modify. I am looking for AAA BBB CCC values in cell Y20, AB20 and BC20.

I have tried several solutions like:

SEARCH(Y20;$CE$1:$CE$3) ------- Where CE1:CE3 is the range containing AAA BBB and CCC COUNTIF(Y20:BC20;$CE$1:$CE$3)

Full formula would be IF(SEARCH(Y20;$CE$1:$CE$3)>0;Y20;IF(SEARCH(AB20;$CE$1:$CE$3)>0;AB20;SEARCH(BC20;$CE$1:$CE$3);BC20;"None")))

Issue is that those formulas are all spilling by the size of the range with referenced values. Like if AAA BBB and CCC where the values to look at, the formula is spilling 2 cells down. If I add DDD it spills 3 cells down.

I tried this one from r/excel : =OR(ISNUMBER(SEARCH(","&Y20&",";","$CE$1:$CE$3&","))) But it returns FALSE when the value is found and TRUE when the cell Y20 is blank, which does not help at building something strong and readable (I have to go back to the original cells in case of true to check which value it found).

I tried that one: =TEXTJOIN(", ";TRUE;IF(COUNTIF(Y20;""&$CE$1:$CE$3&"");$CE$1:$CE$3;"")) But it returns 0,0 in all instances.

I am truly lost there. I know I can do concatenated IFs statements but with 3 possible values checking 3 different cells, it will be a long long IF.

What I am trying to achieve is to check if cells Y20, AB20, BC20 contains either AAA, BBB or CCC, those cells can contain only one of those values. AAA BBB and CCC are in range CE1:CE3

this check will be contained in cell CA20. So for example CA20 should show AAA if it finds AAA in cell Y20, AB20 or BC20.

Could you please guide me on that one?

Thanks a lot in advance for your help.

CodePudding user response：

Or different approach:

=LET(v,VSTACK(Y20,AB20,BC20),
     m,ISNUMBER(MATCH("*"&CE1:CE3&"*",v,0)),
FILTER(v,m,"None"))

Your three cells are stacked as an array and checked to contain the values in CE1:CE3 if so it's value of array is filtered to that value(s).

CodePudding user response：

As I interpret this question:

You have 3 values contained in CE1:CE3
You want the formula to return the first of those values that is found once either in either Y20, AB20 or BC20. If it's found in more than one place, it's not valid.

This formula accomplishes that (will need Office365, earlier versions of Excel don't support these functions)

=LET(
SearchFor,CE1:CE3,
NumFound,BYROW(SearchFor, LAMBDA(row, SUM(Y20=row,AB20=row,BC20=row))),
FoundFilter, FILTER(SearchFor, NumFound = 1),
INDEX(FoundFilter, 1)
)

CodePudding user response：

Thanks for you suggestions. I have no access to LET or VSTACK function in my version of excel. I used a CONCAT(IF(CE1:CE3=Y20) (CE1:CE3=AB20) (CE1:CE3=BC20);CE1:CE3;""))

It worked well. Credits: Reddit anon