Particular Question on using Lambda, Index, and Match to Look Up in a Table

Question

We use lots of sheets that have to look up data in tables based on two variables. I have used the usual look up method, but would like to turn that into an easy-to-use function I can put anywhere. We use this:

=INDEX(A1:H10,MATCH(A15,A1:H1,0),MATCH(A16,A1:A10,0)))

Where:

• A1:H10 is the table of data with the index values on the top row and left column (X and Y, respectively).
• A15 is where the X value to be looked up is.
• A16 is where the Y value to be looked up is.

My first pass at a Lambda function for this is:

=LAMBDA(TL,BL,TR,BR,X,Y,INDEX(TL:BR,MATCH(X,TL:TR,0),MATCH(Y,TL:BL,0)))

Where:

• TL is the top left cell of the table.
• BL is the bottom left cell of the table.
• TR is the top right cell of the table.
• BR is the bottom right cell of the table.
• X is the X lookup value.
• Y is the Y lookup value.

My question is that I would like to streamline this Lambda function so that you only have to input the top left and bottom right cells of the table. The tricky part comes in when you have to put the top row in for the X Match function and the left column in for the Y Match function. The point is, I would like to reduce the inputs to this new function from 6 to 4.

Thanks in advance!

Dave

Answer 1

You switched the row lookup and column indexes: the syntax of INDEX is INDEX(range, row_num, column_num), so MATCH(Y,TL:BL,0) goes before MATCH(X,TL:TR,0).

Define Lookupλ as

=LAMBDA(TL,BR,X,Y, LET(BL, OFFSET(TL, ROW(BR)-ROW(TL)+1, 0), TR, OFFSET(TL, 0, COLUMN(BR)-COLUMN(TL)+1), INDEX(TL:BR, MATCH(Y, TL:BL, 0), MATCH(X, TL:TR, 0))))

Call like this:

=Lookupλ(A1, H10, A15, A16)

Answer 2

Fantastic, HansV! Thanks!