Altering VLookup Formula

[addie]

How would I alter a v-lookup formula to recognize specific characters
within a field?

Example:
Data Field has the following 9 characters "R0502AA01”.

Can V-lookup only pick up or recognize the following 7 characters
"R0502--01”?

How would a v-lookup formula pick up the 1st - 5th and 8th - 9th
characters and bypass the 6th & 7th characters?


Addie

 

[Kevin Vaughn]

The following array entered formula (cntl-shift-enter) worked for me: Note,
if value not found it will return #N/A which you could handle by, for
instance, wrapping the formula in the ISNA function:

=INDEX($B$2:$B$6,MATCH(LEFT(B12,5) & RIGHT(B12,2),LEFT($A$2:$A$6,5) &
RIGHT($A$2:$A$6,2),0),1)

B2:b6 is the range you want returned, A2:a6 is the lookup range (R0502AA01)
and b12 is the lookup value (R0502--01)