Spreadsheet: 736698d1623818089-lookup-backwards-partial-match-example-for-backwards-partial-match-ans
Sheet: Data

 | [NAME] | VENDOR CODE
 | TST* NOTHING BUNDT CTHOUSAND OAKS       CA |  |  | I'd like to figure out a formula for the yellow cells, to lookup the description on the Setup sheet, and return a vendor code (if found).
 | Amazon Marketplace |  | amazon | • If there is an exact match to a [NAME] in Setup sheet, use that entry
 | AT&T EPAY BUSINESS REC |  | att | • If there is NO exact match, check for a partial match, where the first X characters of the Description exactly match [NAME] sheet, and X = LEN(String)
 | HD SUPPLY HIS #728 0SAN FERNANDO        CA
 | [NAME] HLS        CA |  |  | For example, all of the Descriptions that begin with "THE HOME DEPOT" would match the entry on row 7 of the [NAME] sheet
 | THE HOME DEPOT      SAN FERNANDO        CA |  | matls
 | THE HOME DEPOT      THOUSAND OAKS       CA |  | matls
 | THE HOME DEPOT 609  SAN FERNANDO        CA |  | matls
 | THE HOME DEPOT 6644 PANORAMA CITY       CA |  | matls
 | VZWRLSS BILL PAY VW [PHONE]        FL
 | DOORDASH*EXOTIC THAISAN FRANCISCO       CA |  | meals
 | SHELL OIL 5744417830SIMI VALLEY         CA |  | fuel
 | THE HOME DEPOT      GOLETA              CA |  | matls
 | THE HOME DEPOT      SAN FERNANDO        CA |  | matls
 | THE HOME DEPOT      SAN FERNANDO        CA |  | matls
 | THE HOME DEPOT      SAN FERNANDO        CA |  | matls
 | THE HOME DEPOT 6623 GOLETA              CA |  | matls
 | Amazon Marketplace |  | amazon
 | AT&T MOBILE RECURRING W |  | att
 | FEDEX - EXPRESS     [PHONE]        TN |  | fedex
 | AT&T CONSUMER PHONE [PHONE]        IL |  | att
 | AT&T EPAY BUSINESS REC |  | att
 | AT&T EPAY BUSINESS REC |  | att
 | AT&T EPAY BUSINESS REC |  | att
 | AT&T EPAY BUSINESS REC |  | att
 | BT*UNIVERSAL YUMS   PARSIPPANY          NJ
 | BT*UNIVERSAL YUMS   PARSIPPANY          NJ
 | LOGMEIN*GOTOMEETING LOGMEIN.COM         MA
 | THE HOME DEPOT      SAN FERNANDO        CA |  | matls
 | WWW.PROCESS.ST      SAN FRANCISCO       CA
 | WWW.PROCESS.ST      SAN FRANCISCO       CA
 | WWW.PROCESS.ST      SAN FRANCISCO       CA
 | Amazon Marketplace |  | amazon
 | Amazon Marketplace |  | amazon
 | API PLUMBER [NAME]
 | DOORDASH*PLATA TAQUESAN FRANCISCO       CA |  | meals
 | TLG*PRIVGRD68005378M800-374-8273        CT
 | TOBACCO ROYALE CALABCALABASAS           CA
 | API PLUMBER [NAME]
 | [NAME] |  | att
 | [NAME] |  | att
 | Amazon Web Services AWS.Amazon.com      WA
 | CUSTOMINK GROUPS    FAIRFAX             VA
 | DELL INC            ROUND ROCK          TX
 | ENCINO UNION SVC CENENCINO              CA
 | EUROPEAN ELEGANCE WOSHERMAN OAKS        CA
 | EUROPEAN ELEGANCE WOSHERMAN OAKS        CA
 | EXXONMOBIL 9764     TARZANA             CA |  | fuel
 | [NAME]              CA
 | SO CAL FLOWER  SUPPLVAN NUYS            CA
 | CHEVRON 0095348/CHEVAGOURA HILLS        CA |  | fuel
 | DOORDASH*JINKYS CAFESAN FRANCISCO       CA |  | meals
 | MOBILE MINI Mobile MPHOENIX             AZ
 | PACKAIR AIRFREIGHT  [PHONE]        CA
 | QUENCH USA, INC.    [PHONE]         PA
 | THE HOME DEPOT      WEST HILLS          CA |  | matls
 | VOICE NATION        [PHONE]         GA
 | MOBILE MINI Mobile MPHOENIX             AZ
 | FEDEX - EXPRESS     [PHONE]        TN |  | fedex
 | Amazon Marketplace |  | amazon
 | APPLE.COM/BILL      INTERNET CHARGE     CA
 | CHEVRON 0094240/CHEVCALABASAS           CA |  | fuel