VLOOKUP with MID function error

[dcarbajal2006]

I am currently using VLOOKUP with nested MID function. However my look up value sometimes is a number and sometimes is a character. the function i have works for either numbers or character but i can find a way to make it work for either. This is how i have it set up:

VLOOKUP(MID(A2,13,1),Sheet2!F24:G29,2,FALSE) "This works for characters"
VLOOKUP(VALUE(MID(A2,13,1)),Sheet2!F24:G29,2,FALSE) "This works for numbers"

The value of A2 is a 16-Digit part number consisting of numbers and letters
as such: ABC1EFR472AEG10. This is entered by the user on cell A2

The lookup table is as such

| A | BB-4CS5-R4-B |
| B | BB-4CS5-R6MM-B |
| C | BB-4CS5-S4-B |
| 2 | BB-4CSS5-R4-B |
| 3 | BB-4CS5-R6MM-B |
| 4 | BB-4CS5-S4-B |

Please let me know if there is a way to do this when the look up date is either a number or text.

Thank you,

 

[dfsobral]

seems to work
of course that the look up character from the mid function needs to be on the table
in this case G (the 13th character) is not on the information you showed

 

[Marcílio_Lobão]

dcarbajal2006, Good afternoon.

Try to use:

=VLOOKUP(IFERROR(VALUE(MID(A2,13,1)),MID(A2,13,1)), Sheet2!$F$24:$G$29,2,FALSE)

Is that what you want?
I hope it helps.

 

[dcarbajal2006]

seems to work
of course that the look up character from the mid function needs to be on the table
in this case G (the 13th character) is not on the information you showed

Hello,

Good catch! the G was supposed to be a C

thank you,

 

[dcarbajal2006]

Hello Marcilio_Lobao,

Yes! That is exactly what I needed. Thank you very much. I worked!

 

[Marcílio_Lobão]

dcarbarjal2006,

Thanks for the feedback.
Have a nice weekend!