Hi, first post, so bear with me should it not all be to standard!

I have a series of codes which end with "PK2", "PK4", etc.
I need to lookup these against codes in another sheet, without these suffixs, and return data from a corresponding column... so far, so good: (put it on separate lines for sake of easy reading)

=IF(ISNUMBER(SEARCH("PK24",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK36",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK48",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK16",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK6",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK8",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK10",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK12",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK2",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK3",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
IF(ISNUMBER(SEARCH("PK4",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC,28,0),
IF(VLOOKUP(A3,TDM!A:A,1,0)=A3,VLOOKUP(A3,TDM!A:CC,28,0),0))))))))))))

This is all working grand, those with all the various "PK"#'s find the right value, and those without also work a charm... except for codes which don't have any letters / symbols, which return #N/A
(e.g. 900500PK4)

I have tried formatting the cells as text and numbers, (the two lookup cells and the return destination).
As a test I added in a random letter it fixes the problem, but this does little more than prove I know what the problem revolves around, but not how to fix it.

Sure after all this it is fairly easy, but I'm stumped!!

Cheers