How do you create a lookup that will allow a one to many relationship?

[JuliusV]

Attached is the workbook I am referencing.

There is a relationship tab of managers to personnel and then on the populate tab there is a customer and an associated personnel number. I am trying to populate the Manager Column based on the relationship from the relationship tab.

I thought of using an index match, but I believe that is too complicated and it would be a really long if statement. I am trying to find a simple way to do this and may be over thinking how to solve the problem.

 

[Luke M]

You can either fill in the blanks on Relationship sheet between managers, so that every Personnel has a manager name listed, or with current setup, sheet populate, formula would be:
=LOOKUP("ZZZ",Relationship!$A$3:INDEX(Relationship!$A$3:$A$20,MATCH(B2,Relationship!$B$3:$B$20,0)))

Formula works by using idea of "take the last manager that appeared before or at the row where personnel is found. So, red part of formula finds the Personnel via Match, and gives the position to an Index. The blue part of formula anchors the top part of a range, and the Index will be bottom part. The LOOKUP("zzz"... bit then says to find the last word (aka, name) within the range we just defined.

 

[JuliusV]

Hi Luke,

Thanks for the prompt response.

I was wondering is the ZZZ always a trick to find the last letter in a list within the defined range?

Best,

Julius Vazquez

 

[Luke M]

In a way. The lookup is trying to find the item that something is equal to or less than (thinking alphabetically) the lookup value. By using an arbitrary string of "zzz", I pretty much guarantee that it will find the last word. So, it could be "zz", or "zz top", or "ZZZYSJ" or whatever, justs needs to be something that will definitely come last in a sorted list. Does that make sense?

 

[JuliusV]

To clarify, the lookup function will return the last word in a list or the next smallest value (if it is a number)?

Therefore, the "ZZZ" is used to identify the smallest value, which in this case is the last word?

 

[Luke M]

No, not always. Let's assume we had the sorted list
Apple | 1
Banana | 2
Carrot | 3

If we did a lookup for "Banana", it would return 2. If we looked for "Baa", it would return 1, since "Baa" comes before "Banana".

In our example, if we used "ZZZ", since none of the words come after that, it simply goes to the end of list (again, it assume list is sorted), and grabs the last item.

 

[JuliusV]

In your example, how does the lookup know to stop looking at Ban and return the value 1?

Is it comparing only the last letter of the substring and trying to find an equivalent match?

For example,

Money | 1
Cash Money|2
Dollar Bills | 3

If I did a lookup on the following,

Lookup(money) - Return 1 because it is a direct match.
Lookup(M) - Return 1? The first occurrence of an M is returned?
Lookup (C) - Return 2?
Lookup (OLK) - Return 2? Is it returning 2 because the character K is greater than L, therefore it reverts to the previous number.

 

[Luke M]

First, note that if the list isn't sorted, it becomes harder to understand how LOOKUP will work, since it assume the list is sorted. Easier to learn how it's supposed to work with sorted list

 

[Lori]

Further to Luke's explanation on the =LOOKUP("ZZZ",... construction, it may be helpful to consider the following analogy (inspired by a conversation with forum regular Jeff Weir on the divide and conquer method) :

Suppose we are looking to find a T-Shirt in a given size from a large rack. Let's assume the rack is ordered from smallest to largest. We start by selecting one from the middle of the rack, if that one is too small then we move half way up the rack and pick out another; if it's too big we pick one from half way down instead. We can go on repeating these steps until we find one in the right size or find that size is not in stock in which case we pick out the next size down. If it's a really big XXXL size we're after then before long we'll reach the end of the rack and pick out the largest size in stock.

Next suppose the rack has got jumbled up from everyone putting back shirts in random places after finding out that none of them fit. Since we don't know the order we try to find a size that fits via the same method. Looking for the XXXL size we'll keep searching for a larger size until reaching the end of the rack as before although in this case the last one may not be the largest size.

This is essentially how the lookup method is working too. By setting the lookup value to a value that's larger than what is in the array we ensure that the last entry is returned even when the list is unordered.

 

[JuliusV]

Hi Lori,

Thanks for the explanation. Is Lookup essentially a Binary Search Algorithm?

Luke, the only one I do not understand is Lookup(M). Why is it returning 2? Does Lookup only look at the first character (or string of characters if lookup is a string) to determine if the value is found?
My understanding is that lookup looks through each individual character in each string? Therefore, for the list I was assuming 1 would be returned because Cash Money has "M" in it string.
From the explanations it sounds like Lookup is a Key and it is trying to find a matching value. Therefore, it is a key to value relationship.

If Computer Science explanations are easier to get the point across, that works for me too.

Thanks for all the clarification!

Best,

Julius Vazquez

 

[Luke M]

In terms of an alpha-sort, then "m" comes before "money". So, yes, it does look at each individual character, in a sense. But it doesn't not look within a cell, only at the starting characters. When XL sorts (or most programs), you sort by first letter, then 2nd, then 3rd, etc. Does that help? Sorry I don't have exact name for how it works, as I'm mostly self-taught.

 

[JuliusV]

Ok Luke M, I understand how it works. Thank you very much for the explanation!