Solved Find value in a cell and copy a value to another cell

January 26, 2012 at 15:17:45
Specs: Windows XP, pentium 4
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


See More: Find value in a cell and copy a value to another cell

Report •


✔ Best Answer
January 27, 2012 at 19:16:06
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 FELLOPE

Insert 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 FELLOPE

Finally, 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 FELLOPE

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



#1
January 26, 2012 at 16:02:17
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 this return that?"

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


Report •

#2
January 26, 2012 at 17:27:35
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.


Report •

#3
January 27, 2012 at 00:00:03
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.


Report •

Related Solutions

#4
January 27, 2012 at 17:01:41
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.


Report •

#5
January 27, 2012 at 19:16:06
✔ 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 FELLOPE

Insert 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 FELLOPE

Finally, 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 FELLOPE

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


Report •

#6
January 28, 2012 at 15:35:11
Thanks, this help me a lot.

Sammy


Report •

Ask Question