Excel Formula to look up part of the text in a table

3

I have a table of banks and codes in two columns.

Ex:

Banco do Brasil S / A 001

Bank of the State of Rio Grande do Sul - Banrisul 041

Banco Santander do Brasil 033

Banco Mercantil do Brasil 208

Banco Itaú S / A 341

Some expressions are repeated like "Brazil", "Bank", "S / A". So I have to look for specific terms. Sometimes the specific term is in the middle of the name, such as "Rio Grande" or "Mercantil."

I can only use "PROCV" if I know the exact sequence from the beginning of the name, and I run the risk of finding another bank with the same expression. If I know that somewhere in the column there is a bank that has "Rio Grande" in the name, but I do not know how the rest of the name is written, how do I search an entire column for an expression within a text of each cell in column?

    
asked by anonymous 06.04.2017 / 15:05

1 answer

2

You can use PROCV with asterisks. Assuming that the typed field is in cell A1 and your table of banks and codes is in the range A4: B50, for example, the formula would look like this:

=PROCV("*"&A1&"*";A4:B50;2;FALSO)

    
06.04.2017 / 17:22