Altering VLookup Formula

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

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)

--
Kevin Vaughn


"addie" wrote:

>
> 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
>
>
> --
> addie
> ------------------------------------------------------------------------
> addie's Profile: http://www.excelforum.com/member.php...o&userid=25526
> View this thread: http://www.excelforum.com/showthread...hreadid=513012
>
>