Problems with V-Lookup with Partial Match

I'm having a problem with a V-Lookup formula I'm trying to create. I have a Column of SKU's (Column A) and need the V-Lookup to search for the Partial Match in Column "I" and give the answer in Column "B" (based of the match in Column "J"). The problem is that the Data in Column A is not consistent and that I need the V-Lookup to check two columns for the answer.
I tried something like: =VLOOKUP((RIGHT(A2,4)),$I$2:$B$43,2,1), but the problem is there is no consistency with the Column A so RIGHT...,4 doesn't work. I also experimented with =VLOOKUP(A2&"*",$I$2:$J$43,2,0) but that's doesn't give me all needed results.

Does anyone have an idea what I'm missing to get the VLookup to work?

Thank in advance for your help.

Thanks for looking into it Jeff. For the Example in A33 we are looking for M700 as the answer as the FBA-M700 is a different item type. There are often up to 4 different items types with similar SKU's (M700, FBA-M700, USA-M700, FBA-USA-M700). This is what makes it so hard for me to find out the correct formula. In 95% of the cases the item type I'm looking for is the last part of the SKU, but then again, there is no consistency with the SKU's so I can't just follow that rule. Hope this made it more clear.

Thanks,
Sweden12

I think you have to improve the quality of the data in column A. A formula like this in B2:

=VLOOKUP(A2,I:J,2,0)

will give 242 errors (#N/A) out of 257 entries. You could improve that by doing this (say in C2):

=IFERROR(VLOOKUP(A2,I:J,2,0),VLOOKUP(RIGHT(A2,4),I:J,2,0))

but that will still give you 25 errors. A further refinement like this in D2:

=IFERROR(VLOOKUP(A2,I:J,2,0),IFERROR(VLOOKUP(RIGHT(A2,4),I:J,2,0),VLOOKUP(RIGHT(A2,5),I:J,2,0)))

reduces that to 9 errors.

However, in row 17 you have this code - M785 - which doesn't exist in your list. Should it be M755 or M780 ??

Maybe you should take this and then manually look at the errors - maybe you could deduce what the proper entries in column A should be (like in row 23 and 30-32) and manually correct them.

Hope this helps.

Pete

Thanks for your feedback Pete. I think your solution could work. Based on the results in Column D, I can work on getting the errors fixed. I will add what's causing errors and see if that does the trick. Thanks for your help on this.

Sweden12

Thanks for your feedback Glen. There seems to be some minor issue with the Formula. For example the result in B8 with your formula is Triblend instead of Solar SS. The formula reports back RM100 instead of M100. Also, I tried to add some of the missing SKU's but regardless of how I change the formula and activate the Array Formula, the result will always be Not Found. Not sure what I'm doing wrong.

Thanks,
Sweden12

Thanks Glenn. Your solution worked perfectly. I corrected the "Not Found" and now all works the way it should.