Thanks for your help getting functions to work. While I can now define a function, I still am not getting my function to work. Basically I am trying to search a 4 column array, matching in the first 3 columns and returning the resulting 4th column. Here is my code:

Function PRMRate(RateTable As Range, ByVal Customer As String, Pickup As String, Drop As String)
    Dim a As Integer, TestCust As String, TestPickup As String, TestDrop As String
    'Get size of dataset
    lastrow = RateTable.Cells(1, 1).Value
    'Initialize return argument
    PRMRate = 0
    'Loop thru data for complete match of 3 fields
    For a = 3 To lastrow
        TestCust = RateTable.Cells(a, 1).Value
' Is This the Right Customer?
        If (Customer = TestCust) Then
' Is This the Right Pickup point for This Customer?
            TestPickup = RateTable.Cells(a, 2).Value
            If (Pickup = TestPickup) Then
' Is This the Right Drop Point for This Combination?
                TestDrop = RateTable.Cells(a, 3).Value
                    If (Drop = TestDrop) Then
' Yes - Set the Rate for This Combination
                    PRMRate = RateTable.Cells(a, 4).Value
                    Return
                    End If
            End If
        End If
    Next a
End Function

The range I am searching looks like this:

20
CustomerName PickupPoint DropPoint Rate
A C G 1
A C H 2
A C I 3
A D J 4
A D K 5
A D L 6
B E M 7
B E N 8
B E O 9
B F P 10
B F Q 11
B F R 12

It seems pretty simple, but then, too, I'm self-taught, so I'm pretty simple, too, and a novice in VBA. I start the search in the first column If I match in the first column, then I check the second column of the same row for a match. If that matches, then I check the third column of the same row for a match. If that matches, then I return the value in the fourth column of the same row as the function value. Where am I wrong? I get getting a #VALUE error

Thanks for any help anyone can give.

Hi,
I changed your function a bit and it works:

Function PRMRate(ByVal Customer As Variant, Pickup As Variant, Drop As Variant)
    Dim a As Integer, TestCust As Variant, TestPickup As Variant, TestDrop As Variant, LASTROW As Integer
    'Initialize return argument
    PRMRate = 0
    LASTROW = Cells(1, 1)
    'Loop thru data for complete match of 3 fields
    For a = 1 To LASTROW
        TestCust = Cells(a, 1).Value
' Is This the Right Customer?
        If (Customer = TestCust) Then
' Is This the Right Pickup point for This Customer?
            TestPickup = Cells(a, 2).Value
            If (Pickup = TestPickup) Then
' Is This the Right Drop Point for This Combination?
                TestDrop = Cells(a, 3).Value
                    If (Drop = TestDrop) Then
' Yes - Set the Rate for This Combination
                    PRMRate = Cells(a, 4).Value
                End If
            End If
        End If
    Next a
End Function

Thanks for helping, but you simplified a little too much. The range to be searched is actually is on a separate worksheet all by itself. In its final form, it may be a much as 8,000-9,000 rows long, and it is not available for casual viewing because it will contain proprietary info. I've tried different ways to identify the range in the function, but have had no success. Can anyone help? Here is my current attempt:

The function is invoked by PRMRate(CustomerName,PickupPoint,DropPoint). The 3 parameters are simply cells on the calling worksheet that contain text pulldown lists for each of the variables..

Option Explicit
Function PRMRate(Customer As Variant, Pickup As Variant, Drop As Variant)
    Dim a As Integer, TestCust As Variant, TestPickup As Variant, TestDrop As Variant
    Dim lastrow As Integer, RateRange As Range
' Initialize return argument
    PRMRate = 0
' Initialize Rate Sheet Array Reference
    Set RateRange = Worksheets("RateSheet").Range("CustomerRateSheet")
' Get size of dataset
    lastrow = RateRange.Cells(1, 1).Value
' Loop thru data for complete match of 3 fields
    For a = 3 To lastrow
        TestCust = RateRange.Cells(a, 1).Value
' Is This the Right Customer?
        If (Customer = TestCust) Then
' Is This the Right Pickup point for This Customer?
            TestPickup = RateRange.Cells(a, 2).Value
            If (Pickup = TestPickup) Then
' Is This the Right Drop Point for This Combination?
                TestDrop = RateRange.Cells(a, 3).Value
                    If (Drop = TestDrop) Then
' Yes - Set the Rate for This Combination
                    PRMRate = RateRange.Cells(a, 4).Value
                    Return
                    End If
            End If
        End If
    Next a
End Function

Hi,
If I understand well:
You have data (CustomerName,PickupPoint,DropPoint,Rate) in one worksheet ("RateSheet")and in Cell(1,1), in this sheet, You have information about qty of records (rows).Is it correct?
Additionally this sheet is not available for viewing (is it hidden or protected?).
Please explain me why do You need set RateRange. You can't use sheet name instead of RateRange?

Hi,

Thanks for responding. You are correct about the worksheet "RateSheet." I was doing Set RateRange to save keystrokes and to make it easier in the future should I need to change the worksheet name. Ideally, I would like to pass the name into the function, but that hasn't worked either. Any ideas? Her is the code with the Worksheet name RateSheet used:
Terry

Hi,
Change Return on Exit Function

Kazik