Index Match combined with Right where Data in cell contains Alpha not numeric

Hello.

I have been playing with Index Match to lookup values in multiple spreadsheets - either within a workbook or across workbooks and generally don't have an issue.

The situation I have now is where I am trying to match a value in for an Asset Number against a substring of a value that represents a name and contains the Asset Number in the rightmost characters.

The Asset numbers come in 2 variations - nnnnn (5 numerics starting at 00000) and Vnnnn (Alpha + 4 numerics starting at 0000)

The first issue is that the software that produces the extract (CSV) from the application drops the leading zeroes from the all numeric entries.

I could perhaps pad them back out, but in fact all of the data that I am interested in has values over 01000 and I have no collisions with Vnnnn values so I am just interested in the last 4 (will always be Digits).

The second issue I had was that the Index Match did not find any results when adding the Right clause until i added a *1 to it.

The third issue - which I am as yet unable to resolve is that it is not returning the result when using the *1 if the cell contains Vnnnn (i.e. it is not all numeric).

Here is the formula that returns the correct results for all instances where Asset is nnnnn (all numerics) but not if it is Vnnnn.

=INDEX(Snipe!$AD:$AD,MATCH(RIGHT($A:$A,4)*1,Snipe!$C:$C,0))

The data looks like this - and I take the Rightmost 4 characters in Column A on one sheet

To match against this in Column C on another sheet  to do the lookup and return the value in another column

When the value in C starts with a V - the lookup fails on the cells that are 5 characters starting with a V

which makes sense since with the 0 at the end of the Index Match it isn't an exact match.

Oddly it does not return N/A - it returns a 0.

So I tried adding Right (4) to the values in C - but then I got no results at all - every result was N/A?

 =INDEX(Snipe!$AD:$AD,MATCH(RIGHT($A:$A,4)*1,RIGHT(Snipe!$C:$C,4),0))

Likewise this returns N/A :

=INDEX(Snipe!$AD:$AD,MATCH(RIGHT($A:$A,4)*1,RIGHT(Snipe!$C:$C,4)*1,0))

I tried changing the Columns from General to Text and retrying the formulas to no avail.

Is it possible to get this to work without manipulating the raw data using helper cells?

Regards

Steve O.