MacMost Q&A Forum • View All Forum QuestionsAsk a Question

What Formula Can Show the Corresponding Values From ONE Column Into Another?

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

Comments: One Response to “What Formula Can Show the Corresponding Values From ONE Column Into Another?”

    1 year ago

    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.

Comments Closed.