S h o r t S t o r i e s

// Tales from software development

Excel VLOOKUP always treats ? as a wildcard

leave a comment »

Earlier in the week one of our customers provided some data in an Excel spreadsheet.

I needed to match up the coded data values in the spreadsheet with coded values in our database and create some SQL INSERT statements for the mappings.

Rather than do this manually, I executed a SQL query to get a list of the codes from our database and added these to an additional worksheet in the spreadsheet. Then I added a formula on each row of the customer’s worksheet that used the Excel VLOOKUP function to match the customer specified code and the codes in my worksheet and return the key value, located in an offset column, for that row.

Then I added a CONCATENATE formula in another column to build the SQL INSERT statement for each coded value. It all seemed to work rather well until I looked carefully at the INSERT statements generated and realised that some of these were garbage.

The first incorrect INSERT statement mapped a code of ???? to the key value for the DBCT code. How could VLOOKUP match ???? with DBCT ? I looked at the next incorrect INSERT statement – this one was for a code of ????CR and VLOOKUP had matched it with the DBCTCR code.

The customer had used a question mark in their code values to indicate an unknown code value. In other words, ???? simply represented an unknown code and ????CT represented an unknown code that probably ended in CT. I had assumed that VLOOKUP would fail to make a match but slowly it dawned on me that it was actually performing a wildcard match so that ???? was matching the first code with exactly four characters and ????CT was matching the first code with six characters that ended with ‘CT’.

There’s no way of turning this behaviour off so I had to update the lookup formula so that it compared the matched value with the lookup value and, if these were the same, then it performed the lookup to get the key value.

Advertisements

Written by Sea Monkey

May 31, 2011 at 8:00 pm

Posted in General

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: