Covering failed vlookup/matches

 I have a long standing problem.

 Let's say I have a VLOOKUP like the one below:

=VLOOKUP(OFFSET(G4,-$F4+1,-G$3-1)&$F4&"/"&G$3,SurveyData,8,FALSE)

which is to say, a relatively time consuming VLOOKUP, repeated 10,000 times on a sheet.  Over half of these lookups will not be present in the lookup table (it's a sparse dataset). I was hiding the error values using Conditional Formatting, but unfortunately this doesn't help when I want to use further formulas on the results.

I have often used the syntax:

=IF(ISERROR(<COMPLICATED EXPRESSION>), "", <COMPLICATED EXPRESSION>)

The downsides to this approach are

1. it doubles the length of the formula
2. any modifications have to be made twice, and
3. the expression is calculated twice, obviously a performance hit. 

Does anyone else encounter this problem? How do you get around it?  I've thought of two ways:

1. Put <COMPLICATED EXPRESSION> in a separate cell, and just refer to this in the above expression, or
2. Write a function, which just takes a single parameter, and returns either "" or the parameter value.

I don't really like option #1, and I'm unsure of the performance impact of #2.

Hi Rick

one way would be to use COUNTIF (which is fast) to see if the value exists first of all in the first column of SurveyData
=IF(COUNTIF(first_col_surveydata,OFFSET(G4,-$F4+1,-G$3-1)&$F4&"/"&G$3),your_expression,"")

Is there any way you can get rid of Offset in determining your lookup value?
Offset is a volatile function, which will recalculate on any change in the worksheet.
This repeated 10,000 times on your sheet is what is giving you the biggest hit in calculation time, and would be greater than a double Vlookup.

Without knowing your data, I can't advise as to what to use, but if there is any way to use INDEX rather than offset, that would be a great help.
Putting the calculation of the lookup value in another cell (having got rid of the Offset function) would also make the formula far more readable and manageable.

If you have XL2007 available, then the IFERROR() function gets around these lookup problems.

Rick,

This isn't really the solution you're looking for, but I thought I'd post it because it addresses problem 2 in your list.

I've had to deal with the same issue as you, but I've also had to deal with reports where missing data causes a number of other errors.  The best answer I've ever had was the IF(ISERROR()) syntax.  I wrote this macro years ago to quickly convert any formula to that syntax.  When I need to edit the formula I can quickly reconvert it to the IF(ISERROR()) syntax.  It's probably one of my most used macros.

 And looking at it, I realize just how long ago I wrote it.  I'd do it a bit differently today.  But it's worked for me for years.

Sub Error_Blanking() 

 

'****************************************************************'

Transforms each formula in the selected range so that error

'values are not displayed

'**************************************************************** 

 

Dim strOld As String, strNew As String, intCount1 As Integer

Dim c As Range 

 

'Turn ScreenUpdating and Calculation off to speed processing

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual 

 

'Loop through all selected cells

For Each c In Selection

        If c.HasFormula Then 'make sure the cell has a formula

                strOld = c.Formula

                intCount1 = Len(strOld)

       

                'Strip the equals sign from the formula

               strNew = Right(strOld, intCount1 - 1)

               c.Formula = "=IF(ISERROR(" + strNew + ")," + Chr(34) _

                     + Chr(34) + "," + strNew + ")"

 

        Else: End If

 

Next c 

 

'Turn ScreenUpdating and Calculation back on

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True 

 

End Sub

I too am curious regarding the use of OFFSET.  As Roger says, that's going to really hurt performance.  If there's any way around it, I'd suggest you consider it.

David B.

Roger and David,

Thankyou both for your help.

Regarding the use of offset, to she some more light on it's use I will describe the layout of my data:

The cells with the lookup formulas are arranged in tables of 9x9 cells, the tables are laid out 2 across, and 64 down the sheet.  The look-up value for each cell is determined both by what table it is in, and its location within the table.  For the components of the lookup value that vary 'per-table' I have a cell just to the left of the table to reference (C4).  The 'per-cell' values I string together from the row and collumn headings of the table ($F4&"/"&G$3).  I originally had a simple absolute reference to $C$4, which is constant for all cells in the table, Of course when I duplicate the table below I want all the cells in that table to refer to $C$16.  And I didn't fancy changing the absolute reference to each table manually (128 times).  The way I'd overcome this in the past is to use absolute references when coding the first two tables (as there are separate lookup data for tables on the left and tables on the right) then remove all the $'s and filling down.  The only downside to this is that if I need to change the formulas, I need to reinstate the $'s then make the changes and repeat the process.  Admittedly it's a pretty minor downside...

The OFFSET was my (in retrospect) poorly thought out method of getting all the cells to refer to the same 'per-table' cell (e.g. C4, C16) without having to change the formulas between tables.  It used the row and column headings of each table (both just numbered 1 to 9) to 'count back' to C4, C16, etc.. 

So yes, it is possible for me to omit OFFSET, I was just being lazy

Thanks again,

Rick

Rick,

Nothing wrong with being lazy.  I've learned as much about excel as I have because I'm horribly lazy.  Why do things manually when I can automate .

I've even been lazy enough to put a couple of hundred array formulas in a worrkbook.  Of course I thought it was a one time analysis.

The trick is to figure out when Quick and Dirty starts costing you more time that proper development.  If you ever come up with a perfect rule for that, let me know.  I'm still looking. 

David B.