**How about this method? Does it return the values you want? Can it handle your range of values?**
If a match is not found for

*a number followed by an ***single** alpha character then the alpha character is ignored and a match sought for the number itself

The value being looked up AND values in the lookup range can be EITHER

*totally numeric* OR

*numeric followed by single alpha character*
=VLOOKUP(IF(ISNUMBER(A2),A2,IF(COUNTIF(Sheet1!A1:A19,A2)>0,A2,VALUE(LEFT(A2,LEN(A2)-1)))),Sheet1!$A$1:$G$19,2,0)

**Explanation of formula:**
An error is returned if a match is not found for EITHER

*cell value* OR

*cell value minus last alpha*
IF **A2** contains a number then

- VLOOKUP(A2 value)

ELSE **A2** is alpha-numeric

- test with COUNTIF to see if alpha-numeric value is in the lookup range

IF COUNTIF returns 0 then

- remove last character and VLOOKUP(A2 minus last alpha)

ELSE
- VLOOKUP(A2 alpha-numeric value)

RESULTS TABLE

**Excel 2016 (Windows) 32 bit**
| A | B | C |
---|

**1**
| **Look for** | **Result** | ** Formula** |

**2**
| 1012c | ant | =VLOOKUP(IF(ISNUMBER(A2),A2,IF(COUNTIF(Sheet1!$A$1:$A$19,A2)>0,A2,VALUE(LEFT(A2,LEN(A2)-1)))),Sheet1!A$1:$F$19,2,0) |

**3**
| 1012 | ant | =VLOOKUP(IF(ISNUMBER(A3),A3,IF(COUNTIF(Sheet1!$A$1:$A$19,A3)>0,A3,VALUE(LEFT(A3,LEN(A3)-1)))),Sheet1!A$1:$F$19,2,0) |

**4**
| 9991012 | eel | =VLOOKUP(IF(ISNUMBER(A4),A4,IF(COUNTIF(Sheet1!$A$1:$A$19,A4)>0,A4,VALUE(LEFT(A4,LEN(A4)-1)))),Sheet1!A$1:$F$19,2,0) |

**5**
| 101299 | cat | =VLOOKUP(IF(ISNUMBER(A5),A5,IF(COUNTIF(Sheet1!$A$1:$A$19,A5)>0,A5,VALUE(LEFT(A5,LEN(A5)-1)))),Sheet1!A$1:$F$19,2,0) |

**6**
| 101299s | cat | =VLOOKUP(IF(ISNUMBER(A6),A6,IF(COUNTIF(Sheet1!$A$1:$A$19,A6)>0,A6,VALUE(LEFT(A6,LEN(A6)-1)))),Sheet1!A$1:$F$19,2,0) |

LOKKUP TABLE

**Excel 2016 (Windows) 32 bit**
| A | B | C | D | E | F |
---|

**1**
| ** Value** | **Animal** | **c** | **d** | **e** | **f** |

**2**
| 9991012 | eel | *xx* | *xx* | *xx* | *xx* |

**3**
| 1012999 | dog | *xx* | *xx* | *xx* | *xx* |

**4**
| 101299 | cat | *xx* | *xx* | *xx* | *xx* |

**5**
| 101299t | bat | *xx* | *xx* | *xx* | *xx* |

**6**
| 1012 | ant | *xx* | *xx* | *xx* | *xx* |

**7**
| * etc ...* | | | | | |