Forum Discussion

Copper Contributor

May 09, 2023

vlookup and format of texts

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 c...

excel

Formulas and Functions

Rodrigo_

Iron Contributor

May 09, 2023

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.

tkiefert

Copper Contributor

May 11, 2023

Rodrigo_

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.