how do I look up article numbers with spaces and sometimes one or more leading zeroes

Energyneer

New Member
Joined
Jan 9, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello!
I need to look up an article number in a table and return a text associated with this number. A pretty easy task it seems, BUT I can't get it right!

The workbook has two sheets

I recieve article numbers from one program with some content
I past this content in "Materiallista".
In column B I then get the following content:
"RSK 62x xx xx" where x are different digits. Cells format is "general".
Example:
B18: RSK 625 14 14
B19: RSK 625 14 02
B20: RSK 624 07 87
B21: RSK 683 13 10

I have another sheet named "Art nr" with one column with the digits from all article numbers (but without the beginning letters). In other columns there are article names, price and so on.
Column C Column D ... Column F
625 14 14 "Article name"... "Text I want to retrieve"
625 14 02 "Article name"... "Text I want to retrieve"
624 07 87 "Article name"... "Text I want to retrieve"
683 13 10 "Article name"... "Text I want to retrieve"

Column C is formatted as "text". The articles are not in numerical order... Some cells are empty or have a zero

I have tried the approach with
LOOKUP(PI();1/COUNTIF(B18;"*"&'Art nr'!$C$8:$C$469&"*");'Art nr'!$F$8:$F$469)
LOOKUP(PI();1/COUNTIF(B19;"*"&'Art nr'!$C$8:$C$469&"*");'Art nr'!$F$8:$F$469)
and so on

The results are mixed
The first row looking at B18 is ok. I get the text from right row in $F$8:$F$469
Row 19-21 gives me result "0".

If I look at the "countif" function is seems to give multiple results "counts" in $C$8:$C$469
I suspect it is problem with zeroes and spacies. How do I solve this?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Excel Formula:
=VLOOKUP(RIGHT(B18, 9), 'Art nr'!$C$8:$F$469, 4, FALSE)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top