Q on how to search for text in a column

[TheMilkGuy]

Hi folks,

I'm not sure if this matters or not, but I'm using Excel 2007 to
create files used in 2007 and 2003.

I have a list of codes in a column with pertinent data in following
columns. Example of column 1:
CYAB
CYAM
CYYJ
CYYR
CYYT
OTHER

I would like to be able to enter a code on one worksheet and have that
input searched through the column for an exact match so that I can
refer to data along its row. If the code entered does not match any
in the list, I would like to be referred to the "OTHER" line.

I am stymied. Can anyone lend a hand?

Many thanks!
Craig

 

[T. Valko]

Try this:

...........A..........B
1...CYAB....Blue
2...CYAM...Green
3...CYYJ....White
4...CYYR...Brown
5...CYYT...Red
6...OTHER...None

A10 = lookup value

=VLOOKUP(IF(COUNTIF(A1:A6,A10),A10,"Other"),A1:B6,2,0)

 

[T. Valko]

Try this:

...........A..........B
1...CYAB....Blue
2...CYAM...Green
3...CYYJ....White
4...CYYR...Brown
5...CYYT...Red
6...OTHER...None

A10 = lookup value

=VLOOKUP(IF(COUNTIF(A1:A6,A10),A10,"Other"),A1:B6,2,0)

 

[TheMilkGuy]

Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig

 

[TheMilkGuy]

Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig

 

[TheMilkGuy]

Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig

 

[TheMilkGuy]

Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig

 

[TheMilkGuy]

Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig

 

[TheMilkGuy]

Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP


Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP


Hey, that looks great! Thanks Biff, can't wait to try it out.

Cheers!
Craig