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

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

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

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

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

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

node too.

Pierre

1. how to vlookup of one cell by starting 2 digits of another cell?

2. How do I leave formula cell blank if 2nd reference cell is empty?

3. using a vlookup to enter text into rows beneath the vlookup cell

4. view of cell contents changes to #### when cursor leaves cell, ev.

5. How do I get the content of the cell left of the active cell?

6. Move contents of selected cell down one, and to the left one cell?

7. vlookup will not return a cell to the left of the value looked up

8. Can Vlookup check a cell to the left?

9. Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA

10. using VBA remove "0" but leave "X" first digit only please.

11. 16 DIGIT NUMBERS IN CELL WITHOUT LAST DIGIT BEING A ZERO

12. Color a single digit in a mult-digit number cell

13. conditional format on first & last digit on 3 digit cell data

14. XL 2k - all digits in cell turn to 0 after 16th digit

15. How to validate a cell to insert from 9 digit number to 13 digit n

5 post • Page:**1** of **1**