Excel – Providing a Default Value for the Lookup Function

The Lookup, Hlookup, and Vlookup functions in Excel are pretty bad. I found that they die with #VALUE, or #N/A if they don’t find the value you’re looking up, and today I found that my lookup function was only looking for the first word in my lookup value!

So here is a better lookup function to use in Excel when you want it to return a specified default value if your value is not found:

Function Betterlookup(Value As String, LookUpVector As Range, ResultVector As Range, DefaultValue As String) As String
    Dim cell As Range
    Dim i As Integer
    For i = 1 To LookUpVector.Count
        If LookUpVector.Cells(i, 1).Value = Value Then
            goodlookup = ResultVector.Cells(i, 1).Value
            Exit Function
        End If
    Next i
    goodlookup = DefaultValue
End Function

Here’s how to use it (courtesy of ozgrid.com):
From within Excel:
1. Push Alt+F11 and go to Insert>Module
2. Copy and paste in the code.
3. Push Alt+Q and Save.

Now in any cell put in the Function like this:

=Betterlookup("Cat",C1:C20,D1:D20,"Not Found")

  • Where “Cat” is the value to find
  • C1:C20 is the range to look in the first column and find “Cat”
  • D1:D20 is the column to return our result from.
  • “Not Found” is what is used if “Cat” is not found.

Here are some more resources:

  1. http://www.ozgrid.com/VBA/Functions.htm has all sorts of useful looking custom Excel functions for you.
  2. This page will get you started on writing your own functions.

Finally here’s what I searched for before I wrote this myself:

  1. excel user defined function reference cells in other worksheet
  2. excel lookup default value
  3. create your own worksheet functions
  4. create new worksheet functions

Posted by Greg Pinero (Primary Searcher) on Jan 25th, 2006 and is filed under Excel, win32.

Both comments and pings are currently closed.

Add this post to Del.icio.us - Digg

2 Responses to “Excel – Providing a Default Value for the Lookup Function”

  1. Bob Oliver Bigellow XLII says:
    August 23rd, 2006 at 10:52 am

    I use the VLOOKUP function a lot myself, and I just thought I would point out that there might be specific reasons that you are having issues with the standard VLOOKUP function. The first is that the column you are searching for the information should be to the left of the column whose information you want to return, assuming this isn’t the same column. I am going to assume you are already aware of this one. Sometimes it involves duplicating the column if this is not the case. Secondly, VLOOKUP expects the lookup column to be in alphabetical order. That is, UNLESS you give the final parameter as FALSE. For instance, =VLOOKUP(E1,A1:C10,2,FALSE). Lastly, returning N/A when not found is by design. If you are wanting a default instead (such as an empty string) you can use the IF and ISNA functions. This could either be nested with the original VLOOKUP function, or could be placed in an adjacent cell. It is often much easier to create various columns of step-by-step logic until you have reached your final value, then hide the transitional columns. For instance, =IF(ISNA(F1),”default value instead”,F1). Becoming familiar with these aspects of VLOOKUP and the various ways to analyze the results could save a lot of time, rather than having to add a macro function to every Excel sheet you plan to use this feature in.

  2. Greg Pinero (Primary Searcher) says:
    August 23rd, 2006 at 10:58 am

    Thanks for the advice, Bob. I’ll give that a try in my next spreadsheet.