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

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

Post by Pierr » Thu, 07 Dec 2006 02:39:20


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

Post by jlepac » Thu, 07 Dec 2006 02:51:10

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

 
 
 

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

Post by Pierr » Thu, 07 Dec 2006 03:01:45


Voila! Works too well.
Thanks Jason
 
 
 

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

Post by Um9uIENvZG » Thu, 07 Dec 2006 03:02:00

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
 
 
 

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

Post by Pierr » Thu, 07 Dec 2006 03:40:34


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

Pierre