Excel Multiple Criteria Index on same Cell

Question

I hope someone can help me with an excel formula. I have a table with thousands of entries so I am going to try and explain what I am trying to do with different data below.

I would like to be able to enter two names in a cell and have a formula return a result of where these names match in the same location.

This sounds very simple and I may be overthinking this. I have been working with using an index formula with multiple criteria but I haven't been able to come up with a way to keeping the index searching for a match once it finds a name.

Below is my sample data

Location    Name
1   Mark
1   Alex
1   Andy
1   Bob
2   Frank
2   Tony
2   Andy
3   Chris
3   Frank
3   Mark
4   Bob
4   Ben
4   Frank
4   Brian

Result Example-

Name1   Name2   Location
Mark    Frank   3

Since Mark and Frank Both are in Location 3, the result would return 3.

Thank you in advance for the help. Mark

kolcinx kolcinx · Accepted Answer · 2017-09-27T19:33:59

Maybe something to get you going, an array formula:

{=IF(Names=Name1;Locations)}
Enter with CTRL+SHIFT+ENTER to make it an array formula.

The resulting array is this for 'Mark':
{1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3;FALSE;FALSE;FALSE;FALSE}

Hope this helps.

Excel Multiple Criteria Index on same Cell

1 Answers