Forum Discussion
Functions
Is there any function in Excel that would be possible to search for an item in a table that has more than one correspondence, and therefore the correspondence would be chosen randomly? I know about the XLOOKUP function, but this function works well when there is only one match.
That could be like
=INDEX( FILTER(range, (range=value)), n)With your sample FILTER() returns an array of 5 elements and you only shall to say which of them to take, other words what shall be number n as second parameter for INDEX(). Thus you have one element every time.
5 Replies
Perhaps when FILTER(), depends on what you'd like to receive
My need would be: using a function similar to XLOOKUP to find a reference in a table, but this reference would have more than one correspondence. Thus, I believe that I have to create some rules so that each time the reference was found the correspondence would be different. I don't know if there is such a thing in Excel, but it would have to be automatic so that I don't have to force a filter every time. As an example: for a given reference there would be 5 different matches. Is there a way to force Excel to choose a different match each time this reference is needed?
That could be like
=INDEX( FILTER(range, (range=value)), n)With your sample FILTER() returns an array of 5 elements and you only shall to say which of them to take, other words what shall be number n as second parameter for INDEX(). Thus you have one element every time.
