Lookup by deleting last character/Digit

Hi Team,

I am looking for Vlookup formula by removing last digit of the value till we get the result.

Example

I have to vlookup for "Apple" if there is error or no result then formula should check for "Appl" then "App".."Ap".
I can use "LEN" but not sure how to reduce by 1 char everytime if there is no result. As the character or len of the value is not fixed so i can't fix the formula using if error as i may end with long formula and it may still need manual check on lenght.

Thanks in Advance

I've got monster like this:

Formula:

Please Login or Register  to view this content.

accept with Ctrl+Shift+Enter (not just Enter).

and in attached file works (if I correctly understand issue. very interesting by the way).

Please try at G2
=INDEX(C:C,LOOKUP(9^9,MATCH(LEFT(F2,ROW(A$1:INDEX(A:A,LEN(F2))))&"*",B:B,)))

Or this

Formula:

Please Login or Register  to view this content.

Thanks Bo_Ry . But there is issue with the formula. I think i have fixed this with below formula;

=IFERROR(VLOOKUP(F2,B:C,2,0),REPT(VLOOKUP(LEFT(F2,(LEN(F2)-1)),B:C,2,0),(LEN(F2)-(LEN(F2)-1))))

But still i need some experts like you to validate. Also attached file with both the formulas with difference.

Thanks,

How about

=INDEX(C:C,LOOKUP(9^9,MATCH(LEFT(F2,ROW(A$1:INDEX(A:A,LEN(F2)))),B:B,)))

Originally Posted by ravichandavar

KOKOSEK your attachment is not working

Mine solution, looking for whole word and then automatically cut off last character to find out first X characters, then cut off next 'last' etc.
AND it works properly.
Check this attachment.
EDIT: I've found a problem with my formula. Bo_Ry got the same idea but creates much, much simpler.