I have these 2 tables:

On column B i'm trying to get one of the Header Names of a feature that is not empty on Table B. I want it to be selected randomly. The order of the items in Table A can be different than the order of the items in Table B, I'll need some sort of INDEX MATCH here too.

Excel Version: Office 365

Attempted Formula: I tried to base my formula on this:

=INDEX(datarange,RANDBETWEEN(1,COLUMNS(datarange)),1)

but there are more things to consider, like header name if the index match of the same fruit isn't empty, so I know it is more complex.

Any help will be greatly appreciated.

CodePudding user response：

Assuming you have Excel 365 and a volatile result is acceptable:

=LET(
    Fruits, Table_B[Fruit],
    Properties, Table_B[[Red]:[Green]],
    PropertiesHeaders, Table_B[[#Headers],[Red]:[Green]],
    ThisFruit, [@Fruits],

    ThisProperties, FILTER(Properties, Fruits = ThisFruit),
    ThisPropertiesFiltered, FILTER(PropertiesHeaders, ThisProperties <> 0),
    ThisPropertiesCount, COUNTA(ThisPropertiesFiltered),
    IndexRand, RANDBETWEEN(1,ThisPropertiesCount),
    IFERROR(INDEX(ThisPropertiesFiltered,IndexRand),"-")
)

ThisProperties is the row in Table_B for your fruit. I left out the column for the fruit names.

ThisPropertiesFiltered is the names of the properties that the fruit has. I filtered the header names based on if the fruit row had a non-zero value or not.

IndexRand gets a random number between 1 and the number of available properties. Note, if there are zero available properties, ThisPropertiesFiltered returns #CALC! so ThisPropertiesCount will return 1. This is handled later on.

Last we use INDEX to get the random property name. IFERROR returns "-" if no properties were available.

Here are the tables:

Table_A:

Table_B:

CodePudding user response：

Since you have access to dynamic arrays you could try:

Formula in B2:

=LET(X,FILTER(E$1:I$1,INDEX(E$2:I$6,MATCH(A2,D$2:D$6,0),0)<>"","No Feature"),INDEX(X,RANDBETWEEN(1,COUNTA(X))))

Or without LET():

=@SORT(SORT(CHOOSE({1;2;3},E$1:I$1,FILTER(E$2:I$6,D$2:D$6=A2),RANDARRAY(1,5)),3,1,1),2,-1,1)

If you are working through actual tables this should spill down results under Random Feature automatically. However, if one does not use tables, you could nest the above in BYROW() if you are an 365-insider:

=BYROW(A2:A6,LAMBDA(r,LET(X,FILTER(E$1:I$1,INDEX(E$2:I$6,MATCH(r,D$2:D$6,0),0)<>"","No Feature"),INDEX(X,RANDBETWEEN(1,COUNTA(X))))))

This would not work with the 2nd option where we used '@' to parse only the topleft value of our array (implicit intersection).

The idea is that:

• A combination of INDEX() & MATCH() will 'slice' the row of interest out of the lookup-table based on our input.
• In the 2nd step we'd use FILTER() to only leave those headers where the elements from the herefor returned array are not empty. In the case all elements are empty, this function will return the value "No Feature" as a headsup for the users.
• In our final step we combine INDEX() with RANDBETWEEN(). The latter will return a random integer between a LBound (1 in our case) and an Ubound which we based on the amount of returned elements.

I tried to visualize this below.