Partial VLookup return either multiple values or the prefered version

[CaroleIsLostAndConfused]

Okay so we are transitioning from Acomba to a new system. As such we have to do a lot of manual data entry. The problem is that by hand the number will be something like 5285 and the results in the Acomba list will be multiples. Such as 15285-US , CN45285 and MV5285. I must prioritize the last option: MV5285. Is there a way to do this? The only idea I had was doing a partial vlookup return all values in one cell and manually entering the ones that have multiple returns for the right value.

I'm not sure how I can explain it otherwise but I need to search for the value item # and then figure out which item in the other sheet is the right one.

If this can't be done, can someone help me just code the putting all the values in one box so I can manually edit them afterwards?

Thank you so much

 

[Deek6t8]

you could do a nested vlookup using iferror something like if you 5285 is in cell a2 =iferror(vlookup("MV"&a2,M:N,2,0),iferror(vlookup("CN"&a2,M:N,0),vlookup(a2,M:N,2,0),"")) maybe?