Use 2nd digit from the left in cell contents for vlookup
 

Need to do a VLOOKUP and have it look for the 2nd digit from the left
in a cells contents, and find this value in a table,a and return a
result.

iow: If the cells contents are 2B75478A3, it would look for the "B" in
the table; other variations would have the formula look for the 3rd
character from the left or 7.

Table would be B2:G20, column would be 4




TIA for thoughts.

Pierre

Use 2nd digit from the left in cell contents for vlookup
 

Assuming you know how to use Vlookup, you could use the MID function to
extract the specific character.

=MID(A1,2,1)

This instance of Mid exctracts a string from the second character of
cell A1 that is one character long.

I put your funky number in to cell A1 and the formula above into A2 and
voila! the answer is B.

Cheers,
Jason Lepack

Pierre wrote:
Need to do a VLOOKUP and have it look for the 2nd digit from the left
in a cells contents, and find this value in a table,a and return a
result.

iow: If the cells contents are 2B75478A3, it would look for the "B" in
the table; other variations would have the formula look for the 3rd
character from the left or 7.

Table would be B2:G20, column would be 4




TIA for thoughts.

Pierre

Use 2nd digit from the left in cell contents for vlookup
 

jlepack wrote:
Assuming you know how to use Vlookup, you could use the MID function to
extract the specific character.

=MID(A1,2,1)

This instance of Mid exctracts a string from the second character of
cell A1 that is one character long.

I put your funky number in to cell A1 and the formula above into A2 and
voila! the answer is B.

Cheers,
Jason Lepack



Voila! Works too well.
Thanks Jason

Use 2nd digit from the left in cell contents for vlookup
 

Try something like this:

With
A1: (a source string, like 2B75478A3)

This formula looks up the 2nd char from that string in the table at B2:G20
and returns the corresponding value from Col_G

A2: =VLOOKUP(MID(A1,2,1),B2:G20,4,0)

OR...if you want error checking
A2: =IF(COUNTIF(B2:B20,MID(A1,2,1)),VLOOKUP(MID(A1,2,1 ),B2:G20,4,0),"NO
MATCH")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Pierre" wrote:

Need to do a VLOOKUP and have it look for the 2nd digit from the left
in a cells contents, and find this value in a table,a and return a
result.

iow: If the cells contents are 2B75478A3, it would look for the "B" in
the table; other variations would have the formula look for the 3rd
character from the left or 7.

Table would be B2:G20, column would be 4




TIA for thoughts.

Pierre

Use 2nd digit from the left in cell contents for vlookup
 

Ron Coderre wrote:
Try something like this:

With
A1: (a source string, like 2B75478A3)

This formula looks up the 2nd char from that string in the table at B2:G20
and returns the corresponding value from Col_G

A2: =VLOOKUP(MID(A1,2,1),B2:G20,4,0)

OR...if you want error checking
A2: =IF(COUNTIF(B2:B20,MID(A1,2,1)),VLOOKUP(MID(A1,2,1 ),B2:G20,4,0),"NO
MATCH")

Is that something you can work with?
***********
Regards,
Ron


Ron, that does the trick as well! Thanks much for the error checking
node too.

Pierre