How to get a value from another table (similar to dereference), when there is no reference to that table?

My challenge is that I use a “select-formula” (in Table1) to choose a name from another table (Table2).
I have to do it like this, since there is no reference to Table2. This works fine. I get a dropdown of names to choose from.
But in the next row (in Table1) i would like to automaticly pick the matching phonenumber to that name in Table2.
Sounds easy? It’s not! I’ve treid Dereference, Lookup, Any, Select, IF, REF to viritual columns, ++.

I can get a list of phone nubers from table2, but not automaticly picking the one matching the name.

Any suggestions anyone?

Lookup() is most likely the solution you need, just a matter of getting the syntax correct.

Here’s a longer-version explanation of what each entry of the LOOKUP() formula needs:

Lookup(What_to_lookup, what_table_to_look_into, what_column_the_value_will_be_in, what_column_you_want)

To implement something like this, try something like this:

LOOKUP([_thisrow].[Selected_Name], Table2, Table2_Name_Column, Phone_Number)


Here’s a breakdown:

  • [_thisrow].[Selected_Name] - the second part of this element needs to be switched to the name of the column that’s your dropdown (the one where users select a single name);
  • Table2 - this needs to be the name of the table that you’re looking up in;
  • Table2_Name_Column - this needs to be the name of the column (in Table2) where the system will find the value from [_thisrow].[Selected_Name];
  • and finally, Phone_Number - this is the column (from Table2) that you want to pull data from.
2 Likes

Man, that worked!! And so easy too! Thanks a lot:)

2 Likes

You’re very welcome, and Welcome to the Community!

2 Likes

@khuslid
May be remind you that, provided there are multiple records in your table with the same name, LOOKUP will return only the 1st record’s matching value in the list. For a more accurate search, you can also use ANY(SELECT(...)) expression.

2 Likes

Thanks. Yes I know. But in this case thats what I needed.