How do I find a value and insert new value

[SingaporeSling]

I want to search a column for a specific value. On finding it I want to
populate a cell in another column with another defined value. Example:
search finds value in A1 (say 99000012) and populates B1 with new value (say
P35) where 99000012 will always equal P35. My spreadheet is 6500 lines I
have 5 values to identfy and set corresponding new values in the new column.

 

[Myrna Larson]

If B1 is to display a value, then B1 must contain a formula.

It's not entirely clear to me what the relationship between 9900012 and P35 is
(if any), and what you want to see in B1 if the value isn't found, but
if P35 contains the number 99000012, you could use this formula in B1:

=IF(MATCH(P35,A:A,0),P35,"not found")

 

[Biff]

Hi!

Create a two column table that lists the values you're
looking for and it's corresponding value.

Assume that table is in the range G1:H5.

Assume the column of values you want to search is in the
range A1:A6500.

In B1 enter this formula:

=IF(ISERROR(VLOOKUP(A1,G$1:H$5,2,0)),"",VLOOKUP
(A1,G$1:H$5,2,0))

Double click the fill handle to copy the formula down to
B6500.

Then you can do a Copy/Paste Special/Values to convert the
formulas to constants.

Biff

 

[Arvi Laanemets]

Hi

On separate sheet, p.e. LookupSheet, create a lookup table, like
LookupValue CorrespondingValue
99000012 P35
....
(let this table fill to fill p.e. the range LookupSheet!A2:B100)
On your sheet, where you want to create new column, enter into B1 the
formula:
=VLOOKUP(A1,LookupSheet!$A$2:$B$100,2,0)
and copy it down.

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets

I want to search a column for a specific value. On finding it I want to
populate a cell in another column with another defined value. Example:
search finds value in A1 (say 99000012) and populates B1 with new value (say
P35) where 99000012 will always equal P35. My spreadheet is 6500 lines I
have 5 values to identfy and set corresponding new values in the new

column.

 

[Biff]

Hmmm...

I read "P35" as meaning "P35" and not $P$35.

Guess I was wrong.

Biff