Problems with creating a formula for a lookup

[Sweden12]

I'm working on a huge list (over 150,000 rows) where I need a formula to find out the weight of each item. I have tried the "IF" function but I'm very limited in what I know about it so I got stuck in creating the formula and what I came up with I needed to manually change every time the SKU change. Here is what I would need help with:

Column A is the External SKU, Column B is the internal SKU, Column C is the Weight and Column D should be the result. We have a bunch of Item SKU's which are inconsistent in the amount of numbers, letters and special characters. The external SKU needs to be broken down into the internal SKU which then defines the weight of the item. So I would need is some type of formula which would solve the following: If parts of entry in Column A equal one of the entries in B & C then show the exact weight in Column D (See my attached sample). I hope my description is somewhat understandable. I would appreciate any kind of input/help.

Thanks in advance for your help.

[dosydos]

I think this is what you are looking for. Try this formula in D2

Please Login or Register  to view this content.

[Sweden12]

Thank you for your answer dosydos. Your suggestion works, but I forgot to mention an important point. The entries in Column A and Column B do not always match (see new attachment). Column B & C is there to list the different Internal SKU's with their weight, so that I have some sort of pointers for Column A. With your formula, as soon as Column B doesn't match with Column A, I don't get a result. Do you have any idea on how I can enhance your suggestion so that it would work for all the entries in Column A?Anoter SKU Weight.xlsx

[ChemistB]

So what would you expect to be the result in row 123 and why?

Better yet, row 137

[ChemistB]

I still get some errors but that might be because the example isn't a complete list ???

In D2 copied down

=IF(ISNUMBER(FIND("-",A2)),INDEX($C$2:$C$416, MATCH(MID(A2, FIND("-", A2)+1,50), $B$2:$B$416,0)), INDEX($C$2:$C$416, MATCH(A2, $B$2:$B$416,0)))

[Sweden12]

The result in row 123 would be 5.4 as the part from the external SKU (M100AG1) which is what I'm looking for is in Row 154 (Column B) and that equals 5.4 (Column C). So basically what I'm looking for is "If part of External SKU (Column A) can be found somewhere in Internal SKU (Column B) than show the weight of it (Column C) in Result (Column D). Hope this makes sense.

[Sweden12]

yet that's it. It works. The errors are because I haven't added all options to Column B yet. But this is the solution I was looking for. Thank you so much for your help.

[Sweden12]

Ok, I hope I have to ask for the last time. The given solution =IF(ISNUMBER(FIND("-",A2)),INDEX($C$2:$C$423, MATCH(MID(A2, FIND("-", A2)+1,50), $B$2:$B$423,0)), INDEX($C$2:$C$423, MATCH(A2, $B$2:$B$423,0))) works great, but now I came across another issue. Some of the external SKU's have two dashes (BOC-001BOS-M100AG7) and some have 3 dashes (EURO-002-IRE-M700WH4). The solution works only for SKU's with one dash (ABC001-M100AG1).
I have tried changing the formula but did not have success yet. Does someone have an idea on how I can change the formula to search data with one, two and three dashes?

[ChemistB]

Is the sku from Col B always at the end?

[Sweden12]

yes, it's always at the end.

[ChemistB]

Try this formula

=INDEX($C$2:$C$416, MATCH(MID("-"&A2, FIND("^", SUBSTITUTE("-"&A2,"-","^", LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1))+1,50),$B$2:$B$416,0))

[Sweden12]

Thanks again. It works and I have not found any issues this time. Seems I have finally what I need. You saved my weekend.

[ChemistB]

Glad to help.