Stumped: Pull in related values when USEREMAIL() populates in table

[ryanasmith]

Hi there!  My first post here as I'm developing an app to help us track requests in my small business.  I am stumped on an issue and I would love some direction.

• All of my team information is stored in a table called Team (all contact info is in there, including their email addresses in a column labeled as Email).
• I have a table called Requests. For each of those requests, users can submit updates, so I have a related table called Updates.

All of that functionality is golden.  Where I'm having trouble is: I would like the App to automatically record who is submitting an Update in Updates.  Using USEREMAIL() in the Initial Value field shows the email address being returned as you'd expect.  But, once that's identified, I'd like the table to also populate with the user's name and the team their on (both are in the same tow in my Team table, where the email addy is coming from). 

So, it should look like this. 

1. They click on the form to add a Update (record into the Update table) and the form pops up.
2. Based on their credentials. the Email populates automatically (form the Team table using USEREMAIL()). They submit their work and:
3. The submitter's name is populated on the Updates record automatically (from the link to the Team table).
4. The Team is populated on the Updates record (from the link to the Team table).

Does that make sense?  I've tried a LOOKUP() and I thought I had it, but I didn't.  I also tried a SELECT() and I couldn't get there. I would love some direction if you guys have it.  I have a feeling I've been sitting at this monitor way too long and I'm making this overly complicated. 

[Simon_Robinson]

Normally on Apps I include a table called User and in it columns called [Login Email] and [Name].  So assuming you have a similar setup you'd want to use this as the Initial Value

ANY(Select(User[Name],[Login Email]=UserEmail()))

[JPAlpano]

The Lookup() should work.

Bur you have to wrap it inside an Index().

INDEX( Lookup(), 1)

[ryanasmith · Accepted solution]

Hi guys!  I figured this out.  I needed to use dereferencing. This Youtube video was a huge help: https://www.youtube.com/watch?v=VH7MQnhIrVg&t=206s