Energyneer
New Member
- Joined
- Jan 9, 2023
- Messages
- 2
- Office Version
- 365
- Platform
- 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?
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?