For example, I have this simple table on sheet "Login Account":
| User ID | |
|---|---|
| A001 | john.doe@gmail.com |
| B001 | test@example.com |
And then on AppSheet, I'm using Data --> User Settings to set up a simple login form. I can set up a field (column) named EMAIL ADDRESS. The second field (column) is YOUR ID, which I give Auto Compute behavior:
=LOOKUP([EMAIL ADDRESS],"Login Account","Email","User ID")
I also have third field (column) with name EMAIL MIRROR, with formula simply copying the content of the EMAIL ADDRESS:
=(EMAIL)
The result:
| EMAIL ADDRESS | YOUR ID | EMAIL MIRROR |
|---|---|---|
| (empty, initial) | A001 | (empty) |
| abc@def.com | A001 | abc@def.com |
| john.doe@gmail.com | A001 | john.doe@gmail.com |
| test@example.com | A001 | test@example.com |
The YOUR ID column always gives results A001, no matter what email I put on EMAIL ADDRESS (as long as the email exists). But the EMAIL MIRROR always updates successfully to the current EMAIL ADDRESS. Why is it like this? How can I use LOOKUP() correctly to look up a value using other column value and update the display to reflect that result?
I'm following this tutorial, but making necessary changes to fit my table & scenario.
it helped me to use:
[_THISROW].[matchvalue]
instead of just [matchvalue]
In your example:
=LOOKUP([_THISROW].[EMAIL ADDRESS],"Login Account","Email","User ID")
More about the solution here: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Lookup-match-value/m-p/256959