Functions

[IsraelFeferman]

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.

[#2]

@IsraelFeferman 

Perhaps when FILTER(), depends on what you'd like to receive

[#3]

@Sergei Baklan 

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?

[#4 · Accepted solution]

@IsraelFeferman 

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]

@Sergei Baklan

Thank you Sergei, and I apologize for the delay of my answer. 

[#6]

@IsraelFeferman , you are welcome