Why doesn't this expression work?

[djmalaka]

Hi all,
I wrote the following expression to a virtual column as well as a real column, but it didn’t work.

LOOKUP([Surgery Title],“Surgery List”,“Surgery Title”,“Specialty”)

I have a table called “Surgery List”. It has 7 columns, [Surgery Title], [Specify - 1], [Specify - 2], [Specify - 3], [Specify - 4], [Specify - 5] and [Specialty]. I have made a large database in this “Surgery List” table filling surgical titles, their specifications, and then in the [Specialty] column, specialty of each surgery (eg: anterior resection is of Lower GI specialty, Open reduction and internal fixation is of Orthopaedic specialty etc.)
There is another table called “Surgery Logbook” for the purpose of creating a form for the user to fill. It fetches the data from that “Surgery List” table and displays them through ‘dependent dropdown menus’. So it also has the same column arrangement as that of the “Surgery List” table, but they appear as dependent dropdowns. So if I write a ValidIf expression to [Specialty] column in “Surgery Logbook” as

Surgery List[Specialty]

The option to select the specialty appears, but since each surgery title has only one specific specialty, only one option appears for the user to select, which is fine. But what I want is when the user selects a surgery title, the [Specialty] column in the “Surgery Logbook” table to automatically get filled with the [Specialty] value that is there in the “Surgery List”. For that, I wrote that LOOKUP expression in the ‘app formula’. But it didn’t work. Then I created a VC and tried, still didn’t work. Can anyone tell me the issue with that expression?

[Steve]

LOOKUP([_THISROW].[Surgery Title],“Surgery List”,“Surgery Title”,“Specialty”)

See also: LOOKUP() (specifically, the Troubleshooting section)

[djmalaka]

Thank you very much Steve!
Could I ask for another advice from you?

Is there a possibility for me to write an expression in order to fetch the value in the column called [Specialty] from “Surgery List” table in the row number decided by the following expression into the “Surgery Logbook” table’s Virtual Column called [Specialty]?

[Steve]

Yep! Modify that FILTER() expression to be a SELECT() expression instead:

FILTER("Surgery List", ...)

becomes:

SELECT(Surgery List[Specialty], ...)

then wrap SELECT() with ANY():

ANY(SELECT(...))

[djmalaka]

Great stuff! Thank you so much!