"Matrix" lookup User Defined Function

  • I've seen several instances of people looking for a solution to using a "matrix" style lookup. The standard solution is to use a something like:

    1. =OFFSET(Value!$A$2,MATCH(B2,Value!$A$3:$A$5,0),MATCH(A2,Value!$B$2:$F$2,0))

    The problem I've always had with this is that it's flat out intimidating for less experienced users to use. This solution requires three cells and two ranges to get the answer that most people would like to see. This is also difficult to debug due to the use of two nested formulas. I've come up with a User Defined Function (UDF) that works well for this situation. Feel free to critique it at your leisure.

    Your formula then looks something like this:

    1. =MatrixLU(A1:I40000,L7,L8)

    A1:I40000 = the table of data you are accessing
    L7 = the row value to find
    L8 = the column value to find

    This function works well, but it seems kind of clunky with the concatenations and all. I'd love to see if any of you experts could improve on it especially in error handling area.

    Mods - because this is kind of a tip and kind of a request for assistance, please feel free to move it to a more appropriate location if needed.

  • Re: "Matrix" lookup User Defined Function

    I like the idea, but I think the following does the same thing with a lot less code.
    [vba]Function MatrixLU(MatrixRef As Range, RowRef, ColumnRef)
    MatrixLU = MatrixRef(Application.Match(RowRef, MatrixRef.Columns(1), 0), _
    Application.Match(ColumnRef, MatrixRef.Rows(1), 0))
    End Function[/vba]

  • Re: "Matrix" lookup User Defined Function

    Veeerryyy nice. I knew that there was a better way to do this, but I was stuck. This also makes it easy to add in the error checking that I'd like to include. If either the row or column value isn't found, the function returns a message letting the user know where the problem is.

  • Re: "Matrix" lookup User Defined Function

    What I can't quite get my head around is how


    differs from,say


    because if you already know the value of row and column (ie L7 & L8) then I can't see the point of the UDF

    Function MatrixLU(MatrixRef As Range, RowRef, ColumnRef)

    seems no different to

    =OFFSET(any cell,row ref,column ref)

    What surely makes the OFFSET function scary is the dual use of MATCH... can you explain how MATCH would not be needed in your formula if you didn't have the row & column values?

  • Re: "Matrix" lookup User Defined Function

    Here's an example:
    Build a data table with rows labeled with the names of all the Kings of England for the last 1000 years and columns labeled with the names of every "British Invasion" rock group. Populate the table with random numbers. Now tell me what value is at the intersection of Henry VIII and Herman's Hermits. You're making the assumption that you know the number of the row and column before you actually have them. That's what the MATCH formula gives you. You're right about the scariness of the two MATCHes. That's what I've been trying to make more user friendly.

  • Re: "Matrix" lookup User Defined Function

    OK, looking at it again I think i see where you are approaching this from

    Function MatrixLU(MatrixRef As Range, RowRef, ColumnRef)

    where rowref & columnref are possibly Strings or variants (to be searched for using MATCH in the UDF right ?) & not (as I assumed) integers i.e. the number of the row or column.. it's late & friday OK... i know.. 2 many beers after work... blah blah

    OK, then maybe we should specify these as variants at least to stop people like me getting confused... :)

  • Re: "Matrix" lookup User Defined Function

    Quote from Will Riley

    Why not ?

    Both are variants....everything is....

    The universe makes sense now - it's all just variants ;) .

    I noticed that in almost every case where I would use this UDF, I had to check for when one of the values was not found and return something other than an error. So, I made a modification that will allow the user to specify the value to use if the lookup values find nothing.