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.

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")


On Thu, 3 Feb 2005 20:47:06 -0800, "SingaporeSling"
wrote:

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.

Hmmm...

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

Guess I was wrong.

Biff

-----Original Message-----
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")


On Thu, 3 Feb 2005 20:47:06 -0800, "SingaporeSling"
wrote:

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.

.

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

-----Original Message-----
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.
.

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<attarkon.ee
Arvi Laanemets


"SingaporeSling" wrote in message
...
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.