Lookup value from Table, but dynamic column

[FIRmOndI]

Hello,

I have a table named UN with the following dataset as you can see in the picture. There are +100 rows.

Context:

We have multiple parking locations (referring to P1, P2 and P3) where we are allowed to store goods. Column A refers to a unique UN-number of specific goods, Column B represents the name and Column C displays the hazardous class. In columns P1, P2 and P3 you can look up the color-code for storing, or if not permited it says 'Niet toegestaan'.

I want to audit the storing process, so I created an app. I can manually fill in the UN-number (refering as DataCardValue32) and select the parking location (choices are P1, P2 and P3) in a dropdown that is named Dropdown1. Then, I have a field where the result (intersection of relevant row in column A and relevant Column D, E or F) needs to be displayed. For example: What is color code for UN 1120 on P2? This result is 'Blauw'.

I have already looked on multiple websites, but can't find anything that works. For example this thread. When I edit the formulas to my situation it just won't work, which is frustrating. Can anybody help me out?

[FIRmOndI]

Currently I have the following formula, but I get zero result.

If(
    Dropdown1.Selected.Value = "P1";
    LookUp(UN; A = DataCardValue32.Text; D);
    Dropdown1.Selected.Value = "P2";
    LookUp(UN; A = DataCardValue32.Text; E);
    Dropdown1.Selected.Value = "P3";
    LookUp(UN; A = DataCardValue32.Text; F);
    ""
)

This formula uses the If function to check which parking location is selected in Dropdown1 and returns the corresponding result using the LookUp function. The LookUp function searches the UN table for the row where column A matches the DataCardValue32 input and returns the value in column D, E, or F, depending on the selected parking location.

If the selected parking location is not P1, P2 or P3, the formula returns an empty string (""). Seems legit to me, but unfortunately not working.

[v-bofeng-msft]

Hi @FIRmOndI ,

Please try:

If(
    Dropdown1.Selected.Value = "P1";
    LookUp(UN; Nummer = DataCardValue32.Text; P1);
    Dropdown1.Selected.Value = "P2";
    LookUp(UN; Nummer  = DataCardValue32.Text; P2);
    Dropdown1.Selected.Value = "P3";
    LookUp(UN; Nummer  = DataCardValue32.Text; P3);
    ""
)

Best Regards,

Bof

[v-bofeng-msft]

Hi @FIRmOndI ,

Please try:

If(
    Dropdown1.Selected.Value = "P1";
    LookUp(UN; A = DataCardValue32.Text; P1);
    Dropdown1.Selected.Value = "P2";
    LookUp(UN; A = DataCardValue32.Text; P2);
    Dropdown1.Selected.Value = "P3";
    LookUp(UN; A = DataCardValue32.Text; P3);
    ""
)

Best Regards,

Bof

[FIRmOndI]

The formula gives no result. I get these error messages:

Function If returns invalid arguments

Function LookUp returns invalid arguments

Name is invalid, A is not recognized

There is a typo somewhere, but I can't find it.

[v-bofeng-msft]

Hi @FIRmOndI ,

Please try:

If(
    Dropdown1.Selected.Value = "P1";
    LookUp(UN; Nummer = DataCardValue32.Text; P1);
    Dropdown1.Selected.Value = "P2";
    LookUp(UN; Nummer  = DataCardValue32.Text; P2);
    Dropdown1.Selected.Value = "P3";
    LookUp(UN; Nummer  = DataCardValue32.Text; P3);
    ""
)

Best Regards,

Bof

[FIRmOndI]

Again, no result. I made sure that I filled in the texbox and the dropdown (of course...). 

This is the dataset in Microsoft Lists. I already linked it to the app.

[FIRmOndI]

When I manually type the formula, it doens't recognize 'nummer', because it is not displayed in the selection field.

[FIRmOndI · Accepted solution]

It works! I made a mistake myself. When I created the table, I named column A also UN, same name as the table. After the first connection with the app I changed the name to Nummer. So I tried typing UN instead of Nummer in the formula, and it works!

This is the working formula:

If(
    Dropdown1.Selected.Value = "P1";
    LookUp(UN; UN = DataCardValue32.Text; P1);
    Dropdown1.Selected.Value = "P2";
    LookUp(UN; UN  = DataCardValue32.Text; P2);
    Dropdown1.Selected.Value = "P3";
    LookUp(UN; UN  = DataCardValue32.Text; P3);
    ""
)

[v-bofeng-msft]

Hi @FIRmOndI ,

Please try:

If(
    Dropdown1.Selected.Value = "P1";
    LookUp(UN; Title = DataCardValue32.Text; P1);
    Dropdown1.Selected.Value = "P2";
    LookUp(UN; Title = DataCardValue32.Text; P2);
    Dropdown1.Selected.Value = "P3";
    LookUp(UN; Title = DataCardValue32.Text; P3);
    ""
)

Best Regards,

Bof