Procv without the last character

Asked

Viewed 24 times

0

I have a procv, where I wrote the following:

SE(ÉERROS(PROCV($A2;'PLAN2'!$A:$C;1;0));"Não Encontrado";"Encontrado")

It works very well in exact cases of searching for values, but some cases have fled normality, as for example, when the value sought have AST-12236158A and on the table have AST-12236158, in this case the value is not found because of the last character, which is apparently always alphabetical, a letter A, B or C.

This way I need to do the search by deleting the last character, and I can’t simply put the amount of characters in the formula, as I saw in a video, because they are not constant.

I tried the code below, but it doesn’t work because the number of characters isn’t always 12.

=PROCV(DIREITA(A2;12)*1;'PLAN2'!$A:$C;1;FALSO)

What I need is that in case of error, a second check is made deleting the last character. I believe that in the second check, it will be better to use a Localizar, last character and delete it from the Procv.

excel procv

  • Looking over your problem I suggest creating a new column and referring to the column where you do the search, with a formula that eliminates the last character if you have more than 12. Then you do the procv directly in this column created and normalized in 12 characters.

1 answer

-2

=SE(SEERRO(DIREITA(A2;1)*1;0)=0;PROCV(ESQUERDA(A2;NÚM.CARACT(A2)-1);$I:$J;2;0);PROCV(A2;$I:$J;2;0))

This formula evaluates whether the last character is numeral or not, if it cannot touch its base.

  • Thank you I will check the spreadsheet, had received a suggestion from another source and used as below, adding some extra checks: =SE($Z2<>"Haroldo";"";SE($L2="FD/DE Not Found";"";SE(ÉERROS(PROCV($C2;'Tags x Fds and Des'! $B:$I;7;0));PROCV(LEFT($C2;NÚM.CARACT($C2)-1);'Tags x Fds and Des'! $B:$I;7;0);PROCV($C2;'Tags x Fds and Des'! $B:$I;7;0))))

Browser other questions tagged excel procv

You are not signed in. Login or sign up in order to post.