A Solution for Looking Up Things When a Simple VLOOKUP Won’t Do

This post will go over some of the solutions I've thought of to enable automating the HR/Legal Report. I haven't finished yet, namely because there are a bunch more downloads that I'm supposed to receive, and because there are a few issues I still need to work out, but I thought I would provide a work-in -progress look. These downloads are any downloads that any company would get so they are not specific to any one company. And they are not state secrets.

Here's an example file with one tab of download that I'm trying to pull in (there are others but I took them out):

There are two tabs plus some other working tabs: a "Template" tab and a "Minimum Wage" tab.

To keep this post manageable, I'm only going to focus on the use of array formulas so that will entail some discussion on the "Template" and the "Minimum Wage" tab. Now this approach using arrays may change after I finish finalizing the automation of the other tabs and I might move towards using the simpler approach used in the other tabs (which I did not include at this time).

Another thing I should point is I had to strip out the macro (macros can easily carry viruses and I don't want to upload macros; besides, I might not be able to upload a macro file.)

Okay, the essential thing you need to know about the "Template" tab is that cell C3 has a drop down to enable you to pick a state. This capability comes from the Data Validation feature of Excel. Nothing fancy here. The rest of the "Template" tab is broken up into sections that pulls in the relevant legal information for the requested state. For this post, I'm going to focus on the minimum wage because I used special array formulas to pull in the information.

If you go to the "Minimum Wage" tab and look under the state column, column D, you will notice that some states has more than one row of entries and the state title is State plus some other verbiage (California, Oregon, and New York are prime offenders). You will also see that some states have more than one municipalities. So what looked like at first to be a VLOOKUP problem rapidly progressed into a mess.

A way to address this is to start from easy and then progress to harder problems so I started with states that were listed once and then proceed to tackle each issue as I progressed to harder problems. You will notice that first 3 columns in the "Minimum Wage" tab are colored gray: those columns are my columns of formula and did not come with the download. The first column handles the minimum wage rate and is needed because some of the minimum wage rates has a superscript and when you pull that information into the "Template" tab, the superscript becomes a part of the wage rate. New Mexico is a good example. So I used the function TRUNC, with the truncation set to 2, to make sure I actually pull in just the wage rate portion. Column B counts the number of rows for the state listed in D. So for Arizona, Arizona takes up two rows. California - small employers take up one row whereas California - large employers take up 31 rows. Column C counts up the municipalities to make sure that they occur once in the entire column; otherwise, I might have problems pulling in the proper municipality.

Okay, that's the easy part of the discussion. Now let's go back to the "Template" tab and go to the Minimum Wage section. Ignore columns A and B because they come into play for printing purposes. Column C (which has been merged into D) actually pulls in the state. If you set the font color to black, you will see the state. Column C actually has a conditional formatting that will show the state name with it has more than one state names - basically California, Oregon and New York. Unfortunately, you will find rows 32 - 34 all have different formulas and I'm not happy with that. I might come up with a better solution later but for now, it is what I've been able to come up.

Column C incorporates array formulas but very different from what I'm used to:

{=INDEX('Minimum Wage'!$D$2:$D$150,SMALL((IF(ISERROR(FIND($C$3,'Minimum Wage'!$D$2:$D$150)),1000,1))*(ROW('Minimum Wage'!$D$2:$D$150)-ROW('Minimum Wage'!$D$2)+1),1),1)}

I'm going to pick the formula apart. The best way to follow along is to go into the formula in the formula part, highlight the part of the formula you want to look at and press F9. Doing that will show you the result of the highlight part of the formula. You will have to download the file in order to do this.

Why do I have a complicated formula like this? Well, if you set the state to Oregon, you will find that Oregon is not just Oregon but Oregon Nonrural counties and Oregon Rural counties; however, cell C3 says just Oregon. What that complicated formula does is find in the list of states in the "Minimum Wage" tab, column D, all states that have the word "Oregon" in it.

If you highlight ROW('Minimum Wage'!$D$2:$D$150)-ROW('Minimum Wage'!$D$2)+1 in the formula bar, you will find that it gives an array of numbers from 1 to 149 (1, 2, 3, 4, 5, ... , 149). It is basically counting up the rows.

Highlight FIND($C$3,'Minimum Wage'!$D$2:$D$150), and you get (#value, #value, #value, ... ,1,1, ... ,#value) except somewhere in the middle, if you can find it, some 1's instead of #value. We're trying to find "Oregon" in column D and row 86 and 87 contains that word.

Next highlight IF(ISERROR(FIND($C$3,'Minimum Wage'!$D$2:$D$150)),1000,1) and you will get (1000, 1000, 1000, ... ,1,1, ... ,1000). Very shortly you will see why I used 1000.

Highlighting (IF(ISERROR(FIND($C$3,'Minimum Wage'!$D$2:$D$150)),1000,1))*(ROW('Minimum Wage'!$D$2:$D$150)-ROW('Minimum Wage'!$D$2)+1) gives (1000, 2000, 3000,..., 86, 87, 88000, ... , 149000). Here is where we multiply the two arrays (1000, 1000, 1000, ... , 1000) and (1, 2, 3, ... , 149) to get (1000, 2000, 3000, ... , 149000) with the 86 and 87 in the middle.

Next comes SMALL((IF(ISERROR(FIND($C$3,'Minimum Wage'!$D$2:$D$150)),1000,1))*(ROW('Minimum Wage'!$D$2:$D$150)-ROW('Minimum Wage'!$D$2)+1),1). The SMALL function works as SMALL(array or list, 1) to get the smallest number. If you have SMALL(array or list, 2), you get the second smallest number. And so on. If you look at cell C33, you will see SMALL((If....+1),2). So what this highlighted section does is to pick out the smallest or second smallest number (86 or 87). Now you see why I was using 1000 - I was forcing all of the other numbers to be very large. There may be another way around it but this was my solution.

Finally, we add in the INDEXing of column D using the 86th or 87th position of the state with the work Oregon.

So that long formula is how I pull in the state or any phrases with that state into the cell. You could use almost the same process for finding a list of phrases that contain a certain word. You might have to tweak that formula to fit a situation but it will be somewhere along the lines of using an array.

Okay, that's it for tonight. I need to wind down for tomorrow.