Excel Matrix Lookup with Lambda

Gareth Stretton

2 min readJul 30, 2022

This article shares how to make a matrix lookup in Excel with Lambda.

MLOOKUP — A custom Lambda function for Matrix Lookups

Assumptions about you…

You know what a matrix is and want to look up values in one
You already know about Lambda functions (and have Office 365)

Let’s waste no time and jump straight into the code…

The lambda code

=LAMBDA(array, left_header, top_header,
    LET(
        row_array, MAKEARRAY(ROWS(array), 1, LAMBDA(row, col, INDEX(array, row, 1))),
        col_array, MAKEARRAY(1, COLUMNS(array), LAMBDA(row, col, INDEX(array, 1, col))),
        row_index, MATCH(left_header, row_array, 0),
        col_index, MATCH(top_header, col_array, 0),
        INDEX(array, row_index, col_index, 1)
    )
)

Example usage

Formula:

// Showing parameter names...
=MLOOKUP(array, left_header, top_header)// Showing example arguments...
=MLOOKUP(A1:D4, C7, C6)// Show example values...
=MLOOKUP(A1:D4, "Medium", "Square")

How does it work?

If you don’t care about how it is written, skip to the next section.

The lambda function accepts an array that has a heading on the left and top. These are accepted as variables: ‘array’, ‘left_header’, and ‘top_header’. (The names ‘left’ and ‘top’ were chosen to minimize confusion). In the above example, the array is cells A1 to D4. The left_header is ‘Medium’ and the top_header is ‘Square’.

The first step is to create a one dimensional array from the first column, that is from the values (0=Blank, 1=‘Small’, 2=‘Medium’, 3=‘Large’). We’ll need this to find the index of the left_header (‘Medium’).

row_array, 
MAKEARRAY(ROWS(array), 1, LAMBDA(row, col, INDEX(array, row, 1)))

And a one dimensional array from the first row, that is from the values (0=Blank, 1=‘Circle’, 2=‘Square’, 3=‘Triangle’). Likewise, this is needed to find the index of the top_header (‘Square’).

col_array, 
MAKEARRAY(1, COLUMNS(array), LAMBDA(row, col, INDEX(array, 1, col)))

With this in place the indices of the values ‘Medium’ and ‘Square’ can be found. The indices are 2 and 2.

row_index, 
MATCH(left_header, row_array, 0),col_index, 
MATCH(top_header, col_array, 0)

Finally, the intersecting value can be retrieved using the function ‘INDEX’.

INDEX(array, row_index, col_index, 1)

What’s the alternative and why is it better?

The alternative is a long formula that can not be reused. ExcelJet.com provides a good explanation if you to see how it works.

This formula is better because it simple. You don’t have to re-write the formula each time to use it, just provide an array and headings.

Sign-off

Hope you find this helpful and let me know what you think.

—

Post Publish Update

u/AmphibiousWarFrogs from r/Excel suggested a more concise approach that uses VLOOKUP:

=LAMBDA(array,left_header,top_header,
     LET(
         col_array, MAKEARRAY(1, COLUMNS(array), LAMBDA(row, col, INDEX(array, 1, col))),
         col_index, MATCH(top_header, col_array, 0),
         VLOOKUP(left_header,array,col_index,FALSE)
     )
 )

I took another stab at the problem and came up with another approach that is fun but overly complex:

=LAMBDA(array,left_header,top_header, 
  LET(
    col_index, REDUCE(0, array, LAMBDA(acc, val, IFS(acc>0, acc, AND(acc<0, val=top_header), (acc-1)*-1, TRUE, acc-1))),
    VLOOKUP(left_header,array,col_index,FALSE)
  )
)