Thread: customLookup Function (2)

Ok, your's work fine I agree, but what happens when you have your validation selection and index on a different page to the one containing the actual tables...? See my attached file

I think you will need to re-write your function to allow that as Cells(myRow, myCol).Value will always be looking on the worksheet that invoked the function for the result.

Dom

Hi Dom,

My problem comes in when you have the validation selection and index on a seperate sheet from the table with the values.

See attached...

What I said above.

Dom

Sorry, it didnt look like my post was actually posted, will read and reply thanks

But the range that I specify refers to the appropriate sheet, how will this make a difference?

You only specify the ranges for the values that you are trying to match, not the range of the table that holds the data that you are wanting to return the value from. The function is assuming that this is on the worksheet you are invoking the function from.

I'd have a go at re-writing it but I'm really busy today I'm afraid.

Dom

I understand the problem.

Is re-wirting this something that I can pick up off the net, or is it something you have come up with on your own?

You could alter the code to this:

Option Explicit
Function myLookup_Index2(rowValue As String, rowRng As Range, colValue As String, _
    colRng As Range, rngTable As Range) As Double
'Find the intersection between two ranges: Row vs Column refering to two specific values


    Dim myRow As Long, myCol As Long

    On Error GoTo err_handler
    myRow = Application.WorksheetFunction.Match(rowValue, rowRng, 0)
    myCol = Application.WorksheetFunction.Match(colValue, colRng, 0)
    myLookup_Index2 = rngTable.Cells(myRow, myCol).Value
    Exit Function
    
err_handler:
    myLookup_Index2 = CVErr(xlErrRef)
End Function

and then call it using:
=myLookup_Index2(B2,'Lookup Sheet'!B4:B8,B3,'Lookup Sheet'!C3:G3,'Lookup Sheet'!C4:G8)

Note the additional table argument at the end, and the fact that the function does not need to be volatile anymore.

Thanks Romper, works like a charm.

Dom, thanks for the help.