Three Way Lookup

[Jak7217]

Hey All:

Not sure the best way to go about this. I started with how I would write a two way lookup, with index and match.

=INDEX(Sheet1!B3:D5,MATCH(A3,Sheet1!B2:D2,1),MATCH(D2,Sheet1!A3:A5,1))

Would it be possible to expand this if my data had another column identifier? Let's say Sheet1 (data) now has last names in row 1 and I now have two Bob. Can I expand this formula to lookup both Bob and Last name as well as the year?

View attachment 104072View attachment 104075

 

[Jak7217]

I tired to edit my comment, but am not allowed after 10 minutes?. This posted in error and my question was incomplete. Sorry for any inconvenience

 

[zepharoz]

This is just my fix and suggestion. You can use a concatenate to combine the first name and last name or the short "&". For example if A2 contained first name "Bob" and B2 contained last name "Marley", then the formula: =concatenate(A2,B2) would combine them; similarly you can use =A2&B2. This would allow you to have a unique identifier.

The other option is to use a numeric ID such as an employee number. I like this option because some people would have middle names, shortened names, ethnic/cultural names, first names as lastnames and viceversa, and all this could make the use of names confusing.

The last option I can recommend is to use sumproduct. Now I'm still learning this and can't call myself a guru. But it's a very powerful formula.
=sumproduct((A:A=[first name criteria])*(B:B=[last name criteria])*(C:C))
~where A:A is the column of the first names
~B:B is the column of the last names
~C:C is the column you are searching for.

 

[Jak7217]

This is just my fix and suggestion. You can use a concatenate to combine the first name and last name or the short "&". For example if A2 contained first name "Bob" and B2 contained last name "Marley", then the formula: =concatenate(A2,B2) would combine them; similarly you can use =A2&B2. This would allow you to have a unique identifier.

The other option is to use a numeric ID such as an employee number. I like this option because some people would have middle names, shortened names, ethnic/cultural names and could make the use of names confusing.

The last option I can recommend is to use sumproduct. Now I'm still learning this and can't call myself a guru. But it's a very powerful formula.
=sumproduct((A:A=[first name criteria])*(B:B=[last name criteria])*(C:C))
~where A:A is the column of the first names
~B:B is the column of the last names
~C:C is the column you are searching for.

Thank you! A much simpler solution than what I thinking up.