vlookup and format of texts

Copper Contributor

Hi all,

 

I use vlookup ("sverweis" in German) to find a specific entry in an automatically created table. The values in the first column (L) can be like "39783" or "30364DE". As you can see they can be digits only or digits and characters. That's why I formatted this column as "text".

 

The value "39783" I am looking for is mentioned in column G in a string like this:

KASSENZEICHEN 820355504599 39783/mm/6020190258879/3120

 

That's why I use the following formula to search for this entry:

=SVERWEIS(TEXT(TEIL(G4;SUCHEN(" ";G4;20)+1;SUCHEN("/";G4)-(SUCHEN(" ";G4;20)+1));"#");$L:$M;2;0)

 

The English names of the functions in the order of appearance are (I think): vlookup, text, mid, search, so I think the English formula would look like that:

=VLOOKUP(TEXT(MID(G4;SEARCH(" ";G4;20)+1;SEARCH("/";G4)-(SEARCH(" ";G4;20)+1));"#");$L:$M;2;0)

 

As long as a value contains at least one character the above-mentioned formula works fine. But if the value contains digits only I receive a "#NA". If I add a string like 39783 manually or if I "convert" the string with TEXT(...) the formula works fine, too. So it seems that the string is not formatted as text correctly even though Excel marks this string as text. 

 

Does anybody know if that's a bug or a feature?

2 Replies

@tkiefert 

Hello, you can try using the VALUE function to convert the string to a number.
Here's an example formula that you can try:

=VLOOKUP(VALUE(MID(G4,SEARCH(" ",G4,20)+1,SEARCH("/",G4)-SEARCH(" ",G4,20)-1)),$L:$M,2,FALSE)
=SVERWEIS(TEXT(Teil(G4;SUCHEN(" ";G4;20)+1;SUCHEN("/";G4)-(SUCHEN(" ";G4;20)+1));"#");$L:$M;2;0)

In this formula, the MID function is used to extract the part of the string that contains the value you are searching for. The VALUE function is then used to convert this string to a number.

@Rr_ 

As mentioned above the string is not always a value. Not sure what Excel does in such a case. But I found a way to convert all values via "TEXT(...)" so comparing the strings works fine now.

 

Unfortunately now I have another issue: The Excel file is a template which is filled out by our case management system. In the template there's one formula like this:

=WENNFEHLER(SVERWEIS(B2;WENN(D2=InvoiceData!$F:$F;InvoiceData!$C:$F);4;0);"")

In the filled out file this formular was changed to

=WENNFEHLER(SVERWEIS(B2;WENN(D2=@InvoiceData!$F:$F;InvoiceData!$C:$F);4;0);"")

Does anyone know why Excel added the @-sign and how I can prevent it from doing so? Without the @-sign the formula works just fine, but with this sign it doesn't.