Concat and lookup

[Dorinda]

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.

[Drrickryp]

@Dorinda 

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

[Dorinda]

@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

[v-jefferni · Accepted solution]

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.