Specific lookup value from a table on the many side in a one to many relationship

CazzR1

New Member
Joined
Nov 6, 2014
Messages
3
Dear all,

Please consider the following situation.

I have two tables related to each other via one field. It is a one to many relationship. Let's call the table on the one side 'singleTable' and on the many side 'manyTable' and the linked field in the relationship 'link'.

In singleTable I would like to return a field from manyTable called 'returnValue', based on the relationship that exist via ‘link’. However, as it is a one to many relationship, multiple matching ‘returnValue’s exist and they are non-unique.

Thus, I understand why =RELATED(returnValue) gives an error.
Let’s say I would like to return the n-th value of the multiple matching ‘returnValue’s. What do we do? My idea is to use =RELATEDTABLE(manyTable), however this returns a table whereas I need a single value. How do I get the intersection of n-th row and the column with name ‘returnValue’?

Many thanks in advance.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
hmmm... so, obviously its easier to grab the min/max/avg, but you should be able to grab the max of the TOPN (or min of the TOPN, depending on ascending/descending desires). Just beware that TOPN is will return more rows than you expect if there are ties.
 

CazzR1

New Member
Joined
Nov 6, 2014
Messages
3
Dear all, thanks for the replies so far, I can't respond in detail now but would like to express my gratitude. @scottsen , the return value is a string .
 

CazzR1

New Member
Joined
Nov 6, 2014
Messages
3
Dear all,

Finally, please see the following example file:


http://we.tl/0YStsMhrTT

What I'm looking for is a formula for column Salesperson_firstlocation, which should be the first matching location in table Orders for that salesperson . (right now it's data but it should be a calculated field)

I'm also looking for a formula that does the same for the second matching location, third, n-th.....

Thanks a lot in advance.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
This is one of those times i struggle with just.. helping answer the question... vs asking "What in the what!?"

If you put this on your salesperson table, you will get the lowest order number associated with that saleperson (and from there, you can look up the associated city).

If you create these measures:
First:=CALCULATE(MAX(Orders[Order_ID]), TOPN(1, Orders, Orders[Order_ID], 1))
Second:=CALCULATE(MAX(Orders[Order_ID]), TOPN(2, Orders, Orders[Order_ID], 1))
Third:=CALCULATE(MAX(Orders[Order_ID]), TOPN(3, Orders, Orders[Order_ID], 1))

You could then create a calc column that is just [First] (etc) to see that it more or less kinda sorta works. (ties will be weird, and when you have a Third with just 2 orders... you will get a repeat).

Still, I am super wondering WHY IN THE WORLD you need this...? :)
 

miguel.escobar

Active Member
Joined
Dec 7, 2012
Messages
294
this should work if you want to have THAT specific column as a formula:
Code:
=LOOKUPVALUE(Orders[Location],Orders[Salesperson],[Salesperson],Orders[Order_ID],MINX( RELATEDTABLE( Orders),Orders[Order_ID]) )
 

Watch MrExcel Video

Forum statistics

Threads
1,089,948
Messages
5,411,466
Members
403,374
Latest member
PMMHart

This Week's Hot Topics

Top