Any ideas on how this could be done?

R

Ronl

Hi I need a to know if excel can do somethign similar to find and replace,
only replace to a different cell.

For example :

If a3="Dakota" then a5="T" if a3="Durango" then a5="S" if a3="PT Cruiser"
then a5="C"
If b3="Dakota" then b5="T" ......
If c3="Dakota" then c5="T" ......

and so on throughout the entire a3 range.

Any ideas as im desperate!

Thanks Ron
 
A

Arvi Laanemets

Hi

1. way
Into separate sheet, p.e. Classes, enter the table
Class Code
Dakota T
Durango S
PT Cruiser C
You can refer to this table directly, but I prefer dynamic named ranges.
Define a named range
Classes=OFFSET(Classes!$A$2,,,COUNTIF(Classes!$A:$A,"<>")-1,2)

On your original sheet, into A5 enter the formula
=IF(SERROR(VLOOKUP(A$3,Classes,2,0)),"",VLOOKUP(A$3,Classes,2,0))
or when you didn't create the named range, then
=IF(SERROR(VLOOKUP(A$3,Classes!$A$2:$B$3,2,0)),"",VLOOKUP(A$3,Classes!$A$2:$
B$3,2,0))
Copy the formula into wanted range in row 5.

2. way (when number of classes don't exceed 24)
Into A5 enter the formula
=CHOOSE(MATCH(A$3,{"Dakota","Durango","PT Cruiser",...},0),"T","S","C",...)
(instead ... continue both lists)
Copy the formula into wanted range in row 5.
 
M

Max

One way is via VLOOKUP

Set up a lookup table first
in a new Sheet2, in A1:B3
--------------------
Dakota T
Durango S
PT Cruiser C

In Sheet1
-------------

With A3, B3, C3, etc containing the phrases:
"Dakota" or "Durango", etc as posted

Put in A5: =VLOOKUP(TRIM(A3),Sheet2!$A$1:$B$3,2,0)
Copy A5 across to C3

This'll return what is wanted

Or perhaps better with some error-traps,

Put instead in A5:

=IF(A3="","",IF(ISNA(MATCH(TRIM(A3),Sheet2!$A:$A,0)),"",VLOOKUP(TRIM(A3),She
et2!$A$1:$B$3,2,0)))

Copy A5 across to C3 (as before)

The above will return the same results as before,
but now with empty cells or unmatched phrases
returning blanks: "" instead of #NAs
 
Top