Wildcard Cell Reference Indexing-CodePudding

Hi I'm trying to find the region code in the below table per licence plates, I am seeking ways to use cell references using wildcard, it does not register and ends up being a string called *34LB12* instead of actually being a wildcard index.
Below is the licence plate database called LICENCEPLATES

Licence Plate   Region (Preferred Result after Index)
34LB12  La Belle
53DF32  De Fritz

The reference sheet is on another sheet as such called REGIONREF

Region Code Region Name
LB  La Belle
LE  Long Ellie

I have tried indexing, however, even through hard coding "LE" as such: =INDEX(REGIONREF!B1:B, MATCH("LE", REGIONREF!A1:A, 1), 1) it would return "De Fritz".
I hope this is not too confusing, if need be let me know in which ways I need to clarify :)

CodePudding user response：

use in B2:

=INDEX(IFNA(VLOOKUP(REGEXEXTRACT(A2:A, "\d{2}([A-Z]{2})\d{2}"), 
 'Reference Sheet'!A2:B, 2, 0)))

ofc. delete B3

or shorter:

=INDEX(IFNA(VLOOKUP(MID(A2:A, 3, 2), 'Reference Sheet'!A2:B, 2, )))

CodePudding user response：

You can get the middle letters through regex:

=REGEXEXTRACT(A2,"\d{2}([A-Z]{2})\d{2}")

where,

\d stands for digit
[A-Z] stands for any upper case letters
{n} stands for n number of previous element
() is a capture group to extract.

You can then use this inside VLOOKUP:

=ARRAYFORMULA(VLOOKUP(REGEXEXTRACT(A2:INDEX(A2:A,COUNTA(A2:A)),"\d{2}([A-Z]{2})\d{2}"),REGIONREF!A:B,2,0))