vlookup and if

  • Thread starter Thread starter Allen Clark
  • Start date Start date
A

Allen Clark

I have a problem that I am attempting to use Excel as an intermediate step
in performing a data migration from an Access system to a SQL application.
The SQL application has some very strict data format requirements for their
conversion scripts to run I have to find a solution so that I can generate a
tab delimited file with the precise columns and appropriate data for the
approximately 25000 records I am trying to convert to the SQL app.

I have a column called Suffix. In the Access database, Suffix contains
values like "MD", "PHD", "Sr", "Jr", etc.

These same values are in a table in the SQL app called tr_suffix, but each
entry has a numeric value (2=Jr., 3=Sr., 4=II, 5=III, 6=IV, 102=MD, 103=DDS,
104=PHD, etc.). The problem is that in order to import the data, I have to
have the values in the "suffix" column contain the values associated with
each suffix text, therefore I have to convert the "Jr." to 2, etc for all
25000 records prior to importing into SQL. And to add insult to injury, if
there is no value, it must be replaced with a 99.

I have defined a range called suffixt that contains two columns, first
column has the Jr., Sr., etc. and second column has the 2, 3, etc. I added
a column and tried using vlookup as follows:

vlookup(a16,SuffixT,2,False)

This works great for those that are present, all others get the #N/A. I
then tried to use the IF statement as follows:

if(ERROR.TYPE(VLOOKUP(A16,SuffixT,2,False)=7,99,VLOOKUP(A16,SuffixT,2,False)
)

This only returns the error #N/A for all values. What did I miss? I
appreciate any assistance with this matter.

Thanks,
Allen
 
Hi Allen!

Try:
=IF(ISNA(VLOOKUP(A16,SuffixT,2,FALSE)),99,VLOOKUP(A16,SuffixT,2,FALSE)
)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Allen!

Pleased to help and for the confirmation that it's working.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top