help with a macro

[Guest]

I use a macro to change information in csv files before importing into a
l;ivestock program.

The csv files always contains the same columns of info but vary in the
number of rows of information.

Part of my macro is to look at column A and depending on what each cell
contains place a letter in the coreesponding cell column G.

The formula is below but all that is happening is that it fixes cellA1 but
doesn't look at the other entries in column A

=LOOKUP(A:A,{"bull","cow","steer","weaner"},{"M","F","S","U"})

What am I doing wrong

thanks

 

[R..VENKATARAMAN]

macro may not be required
A1 to B4 contains
bull m
cow f
steer s
weiner u


in G1 type
=VLOOKUP(A1,A1:B4,2,FALSE)
and <enter>
and copy G1 to g2 g3 and g4

perhaps better solutiion is avialable.

 

[David McRitchie]

Thanks for providing more insight into the problem, and you are almost there,
but you need to use absolute references, or better to use a defined name table
in an out of the way place like using another sheet, so you don't have to
refer to it as sheet2!$A$1:$B$4

Place your table data in A1:B4 of the other sheet, then with the range
A1:B4 selected type "type_table" without the quotes into the name box
at the left of the formula bar. Or use Insert, name, define to add, change, delete.

The use of A:A to represent the cell in column A on the same row, is too hard
to figure out where it will work in Excel and where it won't work, suggest avoiding that

=VLOOKUP(A1,type_table,2,FALSE)

False indicates unsorted table, but the reason for using is to require
an exact match; and if not an exact match you will get #N/A! if not in table

More information on VLOOKUP Worksheet Function and examples in
http://www.mvps.org/dmcritchie/excel/vlookup.htm

The LOOKUP function will return the exact hit or to a previous entry
(the largest value that is less than or equal to lookup_value)
android would return #N/A! since it is before first entry Bull
male would return F (for Female) because it is between "female" and "stud" in the table
zebra would return U (for weaner) because it is after last entry "weaner" in table.