I am trying to show with a formula in a collumn the corresponding values of 2 other values from another collumn
An update concerning my previous question about this topic.
My database is about tables that has codes that corresponds/translates with a name/title.
In some rows/situations, that code collumn can have more than one code.
I know different formulas how to look up a one value in a collumn but what if that collumn contains more than one values, in this case codes.
How do I recall the corresponding values of multiple source values of one collumn in another collumn?
The steps:
1.
I have a table called Articles.
In the row header, I have 2 collumns
Article code
Article name
Row 2: 10 – Book
Row 3: 20 – Candle
So each Article code corresponds with an Article name.
2.
I have a table called Owners.
In the row header, I have 2 collumns
Owner code
Owner name
Row 2: AB – John
Row 3: AC – Luke
So each Owner code corresponds with an Owner name.
3.
I have a table called Connect.
In the row header, I have 2 collumns
Owner code
Article code
Row 2: AB – 10, 20
Row 3: AC – 10
So each Owner has/corresponds with one or more Articles.
4.
The final table I have is called Overview
In this table I have 4 collums
Owner Code
Owner Name
Article Code
Article name
In this table I only have to write the owner code. The other values will show automatically.
The formula in the collumn Owner name:
=LOOKUP($A2;Owners::A;Owners::Owner name)
The formula in the collumn Article code:
=LOOKUP($A2;Connect::A;Connect::Article code)
That all works great.
But now comes the issue. It concerns thre multiple articles John has.
The formula in the collumn Article name:
=LOOKUP(Article code AB;Articles::A;Articles::Article name)
But the result is one value:
It shows only Book in the collumn Article name in the row of John.
I would expect; Book, Candle
—–
Jurgen
So it seems the real problem you have is returning values from more than one ROW. You have the multiple columns done, but sometimes you have two records that match the lookup term. So instead of matching one row and returning "Book," you want it to match more than one row and return "Book, Candle."
That's tricky. No great way to do it.
You can fake it a bit by adding a column to the lookup table that defines a unique value to each row. So if you have two rows with "Mark" as a value, then it would put "Mark-1" for the first row and "Mark-2" for the second row. You'd use COUNTIF for that with clever relative range values.
Then you can lookup "Mark-1" and get that value, then "Mark-2" and get that value, etc. You can use TEXTJOIN to put them together, though it only looks good with spaces as commas would produce values like "Book,Candle,,".
I made an example for you here:
https://macmost.com/downloads/Question-LookupMultipleRows.numbers.zip
Maybe work on this on a Mac until you get it as you like before working with this on an iPhone with the smaller screen, etc.