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! Go to 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.
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:
The exact value youโre looking for. This should be an expression that produces a value.
Which table to look in. This should be the name of the table, in quotes.
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.
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:
Or:
Or:
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")
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
16 |