Lookup "Backwards" Partial Match

Don't know if this is possible or not. I've played around a bit, so far with no success!
I am trying to create a formula to match credit card charges to related vendor codes, based on the charge description in the credit card transactions data. I have a table of descriptions with matching vendor codes.

Problem: I would like to be able to do partial "backward" matches, if an exact match for a description is not found. For example, charges from Chevron should be matched to their vendor code. But the actual descriptions will be like: "CHEVRON SUNNYVALE CA", "CHEVRON 1234 MAIN ST OMAHA NE", "CHEVRON CITY CENTER RICHMOND VA", etc.
I would like for my lookup table to contain a single entry for "CHEVRON", and set up a formula for matching the descriptions, such that any description where the first 7 characters match "CHEVRON" will match to that related vendor code. This is "backwards" because normally you could do a partial lookup on the "short" entry (with a wildcard) to find the first "long" match in the lookup list. But I'm trying to lookup the "long" entry and find the "short" match for it.

The problem is, the length of the lookup string will vary, because the length of my "short" lookup table entries is not constant. "CHEVRON" is 7 characters, but "EXXON" is 5, "AT&T" is 4, "Amazon" is 6, etc.
So basically, the lookup needs to look for a match where the first X characters of the lookup value exactly match a table entry whose length is X (and where X can be different for each table entry). I do have a helper column set up that contains the lengths of each of the table entries.

I've played around with an array formula something like:

Please Login or Register  to view this content.

but obviously this doesn't work because array formulas need to be some kind of SUM, MAX, etc. And MAX of the above doesn't work since the vendor codes are text values, not numbers.

Is there any way to set up a formula that can look up "CHEVRON SUNNYVALE CA" and match it to "CHEVRON", or look up "AT&T MOBILE" and match it to "AT&T", etc.? I've attached an example showing what I want to do.

Attach a sample worksheet. See instruction at top of page in yellow banner.

In C3 then copied down.

Please Login or Register  to view this content.

No need to spend anything yet!! Use this instead...


=IFERROR(INDEX(Setup!$D$3:$D$10,MATCH(1,INDEX(--(ISNUMBER(SEARCH(Setup!$B$3:$B$10,$B3))),0),0)),"")

copied down.

You're welcome & thanks for the feedback.