cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dorinda
Super User
Super User

Concat and lookup

I have two lists in SharePoint, the first is an employee list of retirees, the second is VendorTable that stores the ID of each of the retirees.

 

What I need to do is lookup the last name of the employee in the Employee list and find the corresponding last name in the Vendor Table.

 

I have tried this and it is not working and a few variations on it as well.

 

LookUp(VendorTable,VendorName in DataCardValue20.Selected.EmployeeName,VendorID)

 

Datacardvalue20 is a choice field not a people picker and the format is LastName, FirstName, so I am wondering if there is a way to lookup that way, I also have two seperate fields one for lastname and one for first name.

 

I am stuck any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Dorinda ,

 

I assume the Items of the Datacardvalue20 is the Employee list, and there are LastName/FirstName columns in the VendorTable. so please try below formula:

LookUp(
       VendorTable,
       Lower(
             Trim(
                  VendorLastName
             )
       ) 
       = 
       Lower(
             Trim(
                  First(
                        Split(DataCardValue20.Selected.EmployeeName,",")
                  )
             )
       )
       &&
       Lower(
             Trim(
                  VendorFirstName
             )
       ) 
       = 
       Lower(
             Trim(
                  Last(
                        Split(DataCardValue20.Selected.EmployeeName,",")
                  )
             )
       ),
       VendorID
)

 

This will compare both LastName and FirstName between the selected full name and the names in the Vendor table and get the corresponding vendor's ID.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
Drrickryp
Super User
Super User

@Dorinda 

Lookup(
       VendorTable, StartsWith(
                                             LastName,DataCardValue20.Selected.LastName
                           ),VendorID
)

 

 

@Drrickryp  this didnt work because the combobox is not a people picker.  How do I apply it that way?  The Names are displayed as Reyes, Dorinda, so I need to match to the Last Name first then if there are more than one, look at the start of the second name to ensure we are pulling the correct persons Vendor ID

Hi @Dorinda ,

 

I assume the Items of the Datacardvalue20 is the Employee list, and there are LastName/FirstName columns in the VendorTable. so please try below formula:

LookUp(
       VendorTable,
       Lower(
             Trim(
                  VendorLastName
             )
       ) 
       = 
       Lower(
             Trim(
                  First(
                        Split(DataCardValue20.Selected.EmployeeName,",")
                  )
             )
       )
       &&
       Lower(
             Trim(
                  VendorFirstName
             )
       ) 
       = 
       Lower(
             Trim(
                  Last(
                        Split(DataCardValue20.Selected.EmployeeName,",")
                  )
             )
       ),
       VendorID
)

 

This will compare both LastName and FirstName between the selected full name and the names in the Vendor table and get the corresponding vendor's ID.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,723)