Confusion using the the LookUp Expression

mbnumba6
Participant II

Hi team,

After reading the LookUp article and watching YouTube videos I am still very confused using the LookUp Expression.

I think my confusion is coming from the “Find Value” part of the Lookup.

First, my aim is to auto generate an address into a table from another table.

Example:

Table A has the following three columns and fields:

Agreement Number / Name / Site Address
123 / Test Name / 1345 Dublin Rd Frankling California 44321

Table B, which is the one I’m using the VLookup in looks like the following:

Agreement Number / Building Name / Site Address

In Table B I wrote my formula as the following:

Lookup(Agreement Number, Table A, Site Address, Site Address)

So what I believe this is saying is Lookup the value by the Agreement Number, Look in Table A for the value, Look in the Site Address column of Table A, and return the matching value in the Site Address column of Table B.

The formula has no errors, but the column “Site Address” no longer shows up in the app for Table B.

PS: I know creating a virtual column and referencing could be easier. But the issue I’m running into is that Table B is already being referenced to another Table (not Table A) that doesn’t have the Site Address column.

Hopefully this is explained well.

Solved Solved
0 9 235
1 ACCEPTED SOLUTION

I would attempt as follows:

LOOKUP([Agreement Number], "Waver Form", "Agreement Number", "Jobsite Address")

So your VC is in Table B, so the way this statement gets interpreted is:

LOOKUP( the value of column [Agreement Number] in this row from this table (i.e. table B), in table “Waver Form” (i.e. table A), match with column “Agreement Number” in table A, return the value from column “Jobsite Address” (from table A))

I think that should do what you want, unless I underestimated my Appsheet skills. Still relatively new myself.

View solution in original post

9 REPLIES 9

Steve
Participant V

This is probably not at all correct:

The arguments given don’t match the columns you gave for Table B.

It also doesn’t look like any of the examples in the LOOKUP() help page.

See also:

Sorry Steve.
I had a typo and its now edited.

Mind checking the example I provided again?

Also the formula does match the examples on the LOOKUP() help page.
Least to me it does.
Below is how it looks in Appsheet. “Waver Form” is “Table A”

Expression Assistant describes how it’s interpreted your expression:

Is its interpretation correct?

No that interpretation is not correct.

That’s where I believe my confusion comes from. The way I was thinking was that “Agreement Number” was the value that connected the two tables so the lookup table would have matching values to search for the Jobsite Address.

The value I want back is the address from the other table.

The four inputs for LOOKUP() are:

  1. The exact value you’re looking for. This should be an expression that produces a value.

  2. Which table to look in. This should be the name of the table, in quotes.

  3. Which column of that table to look in. This should be the name of the column, in quotes. The column named should be one of the columns in the table named by (2). AppSheet will look for the value given by (1) in this column of each row in the table.

  4. Which column of that table has the value you want back. This should be the name of a column of the table named by (2), in quotes. This column will almost always be different than the one named by (3). When AppSheet finds the value given by (1) in the column named by (3) of a row in the table named by (2), it will give you the value contained in the column named by (4) of that same row.

In a nutshell:

  • Find the row in table (2) where the column (3) value is exactly (1) and give me the value in column (4).

Or:

  • Give me the value in column (4) of the row in the table (2) where its column (3) value is exactly (1).

Or:

  • Find (1) in a row of table (2) in column (3) and give me the value in that row’s column (4).

I really do appreciate you typing all of that out. I read over it multiple times and I’m still confused.
I don’t know if screenshots of the two spreadsheets would be easier (its all fake data).

Find (1) in a row of table (2) in column (3) and give me the value in that row’s column (4).

The value I am trying to lookup is:

1245 Empire Ave, Camden, NJ 08103, USA

That is the value in the table I am trying to Lookup in another table based on the person’s “Agreement Number”. Agreement Number is the name of a column as well.
First Screenshot is table 1. That is the table I want to pull the addresses from. So its (2) based on the four inputs. Jobsite Address is (3).

This is the second screenshot. So (4) should refer to “Jobsite Address” to this table’s column. I am trying to pull the address value from the first table (screenshot) and put it in the Jobsite address column of this table (second column).

This is the Lookup Expression I used is Lookup(Agreement Number, “Table A”, “Jobsite Address”, “Jobsite Address”)

I really do think the part I am stuck at is the (1). If Agreement Number doesn’t go there then I’m not sure what does? The actual address?

I would attempt as follows:

LOOKUP([Agreement Number], "Waver Form", "Agreement Number", "Jobsite Address")

So your VC is in Table B, so the way this statement gets interpreted is:

LOOKUP( the value of column [Agreement Number] in this row from this table (i.e. table B), in table “Waver Form” (i.e. table A), match with column “Agreement Number” in table A, return the value from column “Jobsite Address” (from table A))

I think that should do what you want, unless I underestimated my Appsheet skills. Still relatively new myself.

Markus_Malessa that worked like a charm.

The spot where you have “Agreement Number” for the third input was where I was wrong aside from the missing quotes.

Instead of “Agreement Number” I had “Jobsite Address”. When the third input from the LOOKUP() Help sheet said "The name of the column in which to search as literal text value" , I interpreted that as the column that held the Jobsite Address.
And I was reading input 4 as where on table b we wanted that value returned.

Sorry the wording on that sheet was kind of confusing for me, but now it makes sense.

Thank you both for the help.

The only change I would recommend: use [_THISROW]:

LOOKUP([_THISROW].[Agreement Number], "Waver Form", "Agreement Number", "Jobsite Address")
Top Labels in this Space