Jan 29 2021 12:01 PM
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.
Jan 29 2021 12:37 PM
Perhaps when FILTER(), depends on what you'd like to receive
Jan 29 2021 12:51 PM
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?
Jan 29 2021 02:29 PM
SolutionThat 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.
Feb 10 2021 06:25 AM
Thank you Sergei, and I apologize for the delay of my answer.
Jan 29 2021 02:29 PM
SolutionThat 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.