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


--
addie
------------------------------------------------------------------------
addie's Profile: http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=513012

Altering VLookup Formula
 

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