Random of Table Header Name of another table via INDEX MATCH

Question

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.

Answer 1

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.

---