Forum Discussion

Ka_ChunL's avatar
Ka_ChunL
Copper Contributor
Sep 16, 2025
Solved

vlookup issue

Hi Expertise,

I have a problem regarding to vlookup issue. I need to use the staff ID prefix which is the first 3 characters (numeric or alphanumeric) of staff ID to lookup the team. I tried to use vlookup or Index and Match or xlookup, unfortunately, still have error. May i ask your help? Thanks

I tried to use left(staff ID,3) or value(left(staff ID,3) to capture staff id prefix. 

 

Staff IDStaff ID prefixTeam Staff ID prefixTeam
736123736HK 736HK
736124736HK 73AJP
73A12373AJP 73BKR
73B12373BKR   
 
excel
  • Kidd_Ip's avatar
    Kidd_Ip
    Sep 17, 2025

    How about this:

     

    =XLOOKUP(TRIM(LEFT(A2,3)), TRIM(E2:E4), F2:F4, "Not Found")

     

2 Replies

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Sep 17, 2025

    How about this:

     

    =XLOOKUP(TRIM(LEFT(A2,3)), TRIM(E2:E4), F2:F4, "Not Found")

     

Resources