Hi,

Im looking for a example code to:Find a value in a cell, example cell A2 has "FIA 035-1 FR"

I need to find FR in that cell, if is found the cell A1 is need to show FELIPE ROSA.Thanks,

Sammy

✔ Best Answer

You don't need any code. It can be done with a VLOOKUP formula. Here's a short list as an example. You can put this list anywhere you'd like, even on another sheet and even in hidden columns. The cells I picked are just examples.

Let's say you start with this:

A B C 1 FIA 035-1 FR FELIPE ROSA 2 FIA 035-1 TQ TOM QUINCE 3 FIA 035-1 LF LOU FELLOPEInsert a new Column C, place this formula in C1 and drag it down:

=RIGHT(B1,2)

You should now have this:

A B C D 1 FIA 035-1 FR FR FELIPE ROSA 2 FIA 035-1 TQ TQ TOM QUINCE 3 FIA 035-1 LF LF LOU FELLOPEFinally, place this formula in A1:

=VLOOKUP(RIGHT(A2,2), $C$1:$D$3, 2, 0)

The formula will extract the last 2 characters from the value in A2 and use VLOOKUP to return the corresponding name from Column D, e.g.

A B C D 1 LOU FELLOPE FIA 035-1 FR FR FELIPE ROSA 2 FIA 035-1 LF FIA 035-1 TQ TQ TOM QUINCE 3 FIA 035-1 LF LF LOU FELLOPEThe advantage of using a formula is that when one or more names change, all you need to do is edit the 1st and 3rd columns of your table. The RIGHT() function will pull the new initials and the VLOOKUP will still pull the name.

You don't want to be editing VBA code every time a name changes. There's too many chances to make an error, especially if more than one person needs to change the names.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I am assuming that this is just one example of a piece of input data and the desired output. How many if these will you have?

How will the various FR's be related to the various FELIPE ROSA's?

In other words, will there be a table that will tell Excel "If you find

returnthis?"that

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

yes, there going to be 10 in any given day. The think is to find the initials in a cell and write the complete name in another.

And that is what I am looking for...If Find this (in a cell) return that (to another cell)

Tks.

I understand what you are trying to do but you didn't answer my question. In order to return the full name for a set of initials, there must be a list of initials and corresponding names.

Where is that list being stored?

Another question:

Are the initials always the last 2 characters in the cell being searched?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I can put the list in a worksheet, but I prefered them in the code. I was thinking in using an If statement...any suggestion>

the easy the better...

and yes, the initial always the last 2 characters.

You don't need any code. It can be done with a VLOOKUP formula. Here's a short list as an example. You can put this list anywhere you'd like, even on another sheet and even in hidden columns. The cells I picked are just examples.

Let's say you start with this:

A B C 1 FIA 035-1 FR FELIPE ROSA 2 FIA 035-1 TQ TOM QUINCE 3 FIA 035-1 LF LOU FELLOPEInsert a new Column C, place this formula in C1 and drag it down:

=RIGHT(B1,2)

You should now have this:

A B C D 1 FIA 035-1 FR FR FELIPE ROSA 2 FIA 035-1 TQ TQ TOM QUINCE 3 FIA 035-1 LF LF LOU FELLOPEFinally, place this formula in A1:

=VLOOKUP(RIGHT(A2,2), $C$1:$D$3, 2, 0)

The formula will extract the last 2 characters from the value in A2 and use VLOOKUP to return the corresponding name from Column D, e.g.

A B C D 1 LOU FELLOPE FIA 035-1 FR FR FELIPE ROSA 2 FIA 035-1 LF FIA 035-1 TQ TQ TOM QUINCE 3 FIA 035-1 LF LF LOU FELLOPEThe advantage of using a formula is that when one or more names change, all you need to do is edit the 1st and 3rd columns of your table. The RIGHT() function will pull the new initials and the VLOOKUP will still pull the name.

You don't want to be editing VBA code every time a name changes. There's too many chances to make an error, especially if more than one person needs to change the names.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks, this help me a lot. Sammy

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History