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

[Pierre]

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

 

[jlepack]

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]

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

 

[Pierre]

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