How to make a vlookup when the lookup_value has extra characters?

[Heshl]

i have 2 sheets one has the SKU numbers and the other the SKUs with the quantity, and i would like to return the quantity from the 2nd sheet to the first sheet with a vlookup which is simple, the challenge is though all the SKUs on the first sheet starts with a few extra characters but the 2nd sheet not, and it has to stay that way, for example the SKU in the first sheet looks like this: "RCC-RUBBER67" and the SKU on the 2nd sheet looks like this: "RUBBER67".

I tried to use this formula:

=IF(A2="","",VLOOKUP(RIGHT(A2,LEN(A2)-FIND("-",A2)),Sheet2!A:D,2,0))

And it works on most cells but on some i'm getting the #N/A error, and its when the SKU looks like this: RCC-15166

It seams to work only when the SKU is letters but not numbers.

Please find the attached file example.

Any help would be highly appreciated.

[humdingaling]

when you convert the cell like you have in the formula
ie RIGHT(A2,LEN(A2)-FIND("-",A2))

you have converted the cell to text format
to combat this change the lookup field to text and it will return the result

attached is the file

all i did was change the sheet2 column to text

[Heshl]

Yes that works, but in order for the error to go away i have to double click the sku for each cell on sheet2 and hit enter, even after converting the format to text.

is there another formula i can use to do the same job without converting the cell to text, and then the need to manually convert the sheet2 to text..

Please?

[canapone]

Hi all,

@Heshl: in the file you are sharing Sku 15166 is repeated three times. Do you need to sum the relevant values (=727) or to get only the first value (42)?

[Heshl]

all the skus in this file are only examples, and there is no repeated skus, i don't understand where you are going with this

[canapone]

Hi again,

in sheet2 I see:

SKU Quantity
RUBBER67 500
RUBBER68 0
RUBBER69 100
RUBBER70 50
15166 42
15166 500
15166 100
15166 85

Anyway, integrating the formula you are using

=IFERROR(IFERROR(VLOOKUP(RIGHT(A2,LEN(A2)-FIND("-",A2)),Sheet2!A:D,2,0),VLOOKUP(RIGHT(A2,LEN(A2)-FIND("-",A2))+0,Sheet2!A:D,2,0)),"")

Hope it helps

[Heshl]

oh you right about the repeated skus, i just dragged the first one down, seams the numbers didn't change, it was supposed to be like this:

15166 42
15167 500
15168 100
15169 85

and your integrating works perfectly for my need, thanks so much..

can you also explain how the 2nd vlookup with the +0 helps?

[canapone]

Hi,

first of all, please forgive my poor English and thanks for sharing feedback


The segment:

RIGHT(A2,LEN(A2)-FIND("-",A2))

produces a string, but if you add a zero it produces a number.

First VLOOKUP tries to match strings, if it fails, the second VLOOKUP tries to match numbers.


Hope it helps

[Heshl]

Thanks a million it helps a lot, this site is amazing.. at this time to get help.

[canapone]

Hi and thanks again,

greetings from Firenze.