I’m surprised that Excel still doesn’t have a simple function 2D_LOOKUP(array, row, col). For instance, I have a complicated excel sheet I’m managing and I frequently want to pull the value for a given parameter on a given date.
As a specific example, I want to turn:
| Date1 | Date2 | Date3 | Date4 | |
|---|---|---|---|---|
| Row1 | A | B | C | D |
| Row2 | E | F | G | H |
| Row3 | I | J | K | L |
And call 2D_LOOKUP(Array, Row2, Date2) and get back F.
However, thanks to lambda I can now define such a function. There are various ways to do it – using XLOOKUP, using INDEX and MATCH, using VLOOKUP etc.
I am curious what is the most optimal way to do it. The code itself could be complicated, since my plan is to just define it in the name manager via a LAMBDA and forget about it from here on out.
I saw https://medium.com/@gareth.stretton/excel-matrix-lookup-with-lambda-30a345472152 which seemed to have an excellent solution, but it used MAKEARRAY and I wasn’t sure how efficient that was.
To be clear, I’m looking for a complete solution – I want code that I (and subsequent readers) can simply drop straight into the Name Manager.
Thank you!
/YGA
